Re: [GENERAL] Questions on dynamic execution and sqlca

2014-08-07 7:24 GMT+02:00 David Johnston : > >> > > - What are the differences among PL/SQL, PL/PGSQL and pgScript. >> > >> > The first two are languages you write functions in. pgScript is simply >> an >> > informal way to group a series of statements together and have them >> execute >> > with

Re: [GENERAL] Connecting with PostgreSQL 9.1 using the Ubuntu machine user and peer authentication method

On Thu, Aug 7, 2014 at 1:14 AM, Adrian Klaver wrote: > On 08/06/2014 03:43 PM, Jorge Arevalo wrote: > >> Hello, >> >> I want to connect to my local installation of PostgreSQL 9.1 using my >> machine user (who is vagrant). So, after reading PostgreSQL >> documentation, I thought I just needed to:

[GENERAL] Upgrading 9.0 to 9.3 - permission denied to pg_upgrade_utility.log

Hi, We're trying to upgrade a 9.0 database to 9.3 using pg_upgrade. The upgrade must be automated so a manual pg_dump / pg_restore is not an option. We use the following command: D:\PostgreSQL\9.3\Data>"c:\Program Files (x86)\PostgreSQL\9.3\bin\pg_upgrade.exe" -d "D:\PostgreSQL\Data" -D "D:\Pos

Re: [GENERAL] Connecting with PostgreSQL 9.1 using the Ubuntu machine user and peer authentication method

On Thu, Aug 7, 2014 at 1:08 AM, John R Pierce wrote: > On 8/6/2014 3:43 PM, Jorge Arevalo wrote: > >> >> I want to connect to my local installation of PostgreSQL 9.1 using my >> machine user (who is vagrant). So, after reading PostgreSQL documentation, >> I thought I just needed to: >> >> > > wou

Re: [GENERAL] Recursive CTE trees + Sorting by votes

Hello Martijn, Thanks for the reply, my responses are inline below. On Wed, Aug 6, 2014 at 5:38 PM, Martijn van Oosterhout wrote: > On Wed, Aug 06, 2014 at 05:28:09PM -0400, Gregory Taylor wrote: > > We are working on a threaded comment system, and found this post by > Disqus > > to be super he

Re: [GENERAL] Recursive CTE trees + Sorting by votes

On 08/07/2014 01:22 PM, Gregory Taylor wrote: > I got this recommendation from someone else, and think that it's > probably the way to go. I've been playing with it unsuccessfully so far, > though. Most certainly because I've got something weirded up. Here's > what I have: > > > WITH RECURSIV

Re: [GENERAL] Recursive CTE trees + Sorting by votes

On Thu, Aug 7, 2014 at 8:12 AM, Vik Fearing wrote: > > Just export the order from your CTE. > > WITH RECURSIVE tree AS ( > SELECT dr.id, >..., >array[dr.id] as path, >1 as depth, >row_number() over (order by dr.num_votes desc) as sort_order >

Re: [GENERAL] Reindex taking forever, and 99% CPU

Phoenix Kiula wrote: > We spent some time to do some massive cleaning of the data from > this table. Brought it down to around 630 million rows. Overall > size of the table including indexes is about 120GB anyway. Deleting rows that you don't need is good, and once a vacuum has a chance to run (

Re: [GENERAL] Connecting with PostgreSQL 9.1 using the Ubuntu machine user and peer authentication method

On Aug 6, 2014, at 6:56 PM, Frank Pinto wrote: > > and make sure you restarted the server so your changes take effect. > reload will get it, also, and is sometimes easier to come by. The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you e

Re: [GENERAL] Connecting with PostgreSQL 9.1 using the Ubuntu machine user and peer authentication method

On 08/07/2014 01:39 AM, Jorge Arevalo wrote: What OS are you on? Per: http://www.postgresql.org/__docs/9.1/interactive/auth-__methods.html#AUTH-PEER "Peer authentication is only available on oper

Re: [GENERAL] Adding 3 hours while inserting data into table

On 08/06/2014 11:29 PM, Gaurav Tiwari G wrote: Hi, Plz find my comment inline below. One more point, if we direct fired insert query, then it is working fine but with application, it is showing problem. Points at a configuration issue in the application. Regards, Gaurav Tiwari > We a

Re: [GENERAL] Adding 3 hours while inserting data into table

On 08/07/2014 07:49 AM, Gaurav Tiwari G wrote: Hi, 1. Postgres configuration might be the suspect area but as we are not specifying any timezone related property. I was not talking about Postgres, it is not the application but the server. I was referring to whatever Java application you are r

[GENERAL] order by question

Hello, I am confused by how postgres 8,4..13 is sorting my data. \d test Table "public.test" Column | Type | Modifiers +--+--- data | text | select * from test order by data; data -- - -- 1 11 11F 1F a b C F -F Feneric Generic (14 rows) Th

Re: [GENERAL] Adding 3 hours while inserting data into table

On 08/07/2014 08:09 AM, Gaurav Tiwari G wrote: Hi, Java application configuration has no issue, we have cross check it. On third point, what data you require as you mentioned that "what is being provided to the Postgres server and what the result is ". I think postgres.conf would be sufficie

Re: [GENERAL] order by question

Steve Clark wrote: > I am confused by how postgres 8,4..13 is sorting my data. > select * from test order by data; > data > -- > >   - >   -- >   1 >   11 >   11F >   1F >   a >   b >   C >   F >   -F >   Feneric >   Generic > (14 rows) > > The first row is a single space, the next r

Re: [GENERAL] The dangers of streaming across versions of glibc: A cautionary tale

We are currently running with the en_US.UTF-8 collation. It was a decision made long ago, and seeing as we never actually rely on the sort order of internationalized strings (other than for stability, apparently), we have never had any motivation to change this practice. Some way of versioning

Re: [GENERAL] order by question

On 08/07/2014 11:36 AM, Kevin Grittner wrote: Steve Clark wrote: I am confused by how postgres 8,4..13 is sorting my data. select * from test order by data; data -- - -- 1 11 11F 1F a b C F -F Feneric Generic (14 rows) The fir

Re: [GENERAL] Recursive CTE trees + Sorting by votes

> Or another idea, add a column that is the path of the parent: I don't think this will work. The problem is you need the full path to keep the children with their parents, but you also need the score. If you make the path an array of (-votes, id) tuples (perhaps flattened for simplicity), then yo

Re: [GENERAL] order by question

Steve Clark wrote: > It is like the space character and the - in -Letter is ignored. Yes, that is how the en_US collation is defined.  I think the goal is to make it something like "phone book" ordering.  If you still have a "white pages" book around, look at how a business name with a hyphen (o

[GENERAL] dump/restore with a hidden dependency?

I have a database with the following structure: Create table bar... Create function subset_of_bar ... (which does a select on a subset of "bar") Create table foo... Alter table foo add constraint mycheck check subset_of_bar(id); I pg_dumped my database, and tried to pg_restore it on another serve

Re: [GENERAL] Adding 3 hours while inserting data into table

Hi, Plz find my comment inline below. One more point, if we direct fired insert query, then it is working fine but with application, it is showing problem. Regards, Gaurav Tiwari -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, Augus

[GENERAL] ENCODING = 'LATIN1' LC_CTYPE?

Dear list, I'd like to create a database with ENCODING LATIN1 . CREATE DATABASE z_latin1 WITH OWNER = admin ENCODING = 'LATIN1' TABLESPACE = pg_default LC_COLLATE = 'German_Germany.1252' LC_CTYPE = 'German_Germany.1252' CONNECTION LIMIT = -1; FEHLER: Kodierung

[GENERAL] {xml}

Hello, when i ran following query on postgres 9.3, SELECT xmlagg(xmlelement(name e,part_id||',')) from part; result .. {xml} how to get part_id's..? please let me know .. advance thanks, R..

Re: [GENERAL] Adding 3 hours while inserting data into table

Hi, Java application configuration has no issue, we have cross check it. On third point, what data you require as you mentioned that "what is being provided to the Postgres server and what the result is ". I think postgres.conf would be sufficient or any other files will be required ?? Regar

Re: [GENERAL] Adding 3 hours while inserting data into table

Hi, 1. Postgres configuration might be the suspect area but as we are not specifying any timezone related property. 2. We are using Postgres Jdbc4 driver. 3. When Application starts everything is running fine, but after few minutes issue occurs and issue remain persist until application rest

Re: [GENERAL] Upgrading 9.0 to 9.3 - permission denied to pg_upgrade_utility.log

On Thu, Aug 7, 2014 at 09:42:13AM +0100, Russell Keane wrote: > Hi, > > > > We’re trying to upgrade a 9.0 database to 9.3 using pg_upgrade. The upgrade > must be automated so a manual pg_dump / pg_restore is not an option. > > We use the following command: > > D:\PostgreSQL\9.3\Data>"c:\Prog

Re: [GENERAL] dump/restore with a hidden dependency?

On 08/07/2014 10:00 AM, Chris Curvey wrote: I’ve done some searching and am coming up empty. Is there a way to get pg_restore to apply constraints AFTER loading all the tables Kinda. PostgreSQL applies constraints with hidden system-level triggers. An easy way to turn them off is to use this

Re: [GENERAL] The dangers of streaming across versions of glibc: A cautionary tale

On Thu, Aug 7, 2014 at 03:07:04PM +, Matthew Kelly wrote: > We are currently running with the en_US.UTF-8 collation. It was a decision > made long ago, and seeing as we never actually rely on the sort order of > internationalized strings (other than for stability, apparently), we have > ne

Re: [GENERAL] dump/restore with a hidden dependency?

On 08/07/2014 01:09 PM, Chris Curvey wrote: The "disable trigger" statement runs without error, but does not seem to have any effect. :( Apparently this trick only works for disabling foreign keys. I'm not sure how to temporarily disable check constraints. You might have to drop the constra

Re: [GENERAL] dump/restore with a hidden dependency?

> -Original Message- > From: Shaun Thomas [mailto:stho...@optionshouse.com] > Sent: Thursday, August 07, 2014 12:43 PM > To: Chris Curvey; pgsql-general@postgresql.org > Subject: Re: [GENERAL] dump/restore with a hidden dependency? > > On 08/07/2014 10:00 AM, Chris Curvey wrote: > > > I'v

Re: [GENERAL] dump/restore with a hidden dependency?

Chris Curvey writes: > I have a database with the following structure: > Create table bar... > Create function subset_of_bar ... (which does a select on a subset of "bar") > Create table foo... > Alter table foo add constraint mycheck check subset_of_bar(id); Basically, that's broken in any numbe

Re: [GENERAL] Avoid WAL archiving when idle?

On Wed, Aug 6, 2014 at 8:49 PM, Laurence Rowe wrote: > I have WAL archiving setup on Postgres 9.3.2 using WAL-E on CentOS 6.4 > using the postgresql.org RPM. This is working fine, except I see a lot of > spurious activity in the S3 bucket with wal files being backed up every 5 > minutes even when

[GENERAL] not finding rows using ctid

Hi, I am getting the logs as follows: LOG: process 32145 acquired ExclusiveLock on tuple (153420,5) of relation 663326 of database 475999 after 1123.028 ms But, when I am executing sqls to find the row on that table using the ctid = '(153420,5)', I get no rows. Any idea, why? Thanks.

Re: [GENERAL] dump/restore with a hidden dependency?

> -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Thursday, August 07, 2014 2:50 PM > To: Chris Curvey > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] dump/restore with a hidden dependency? > > Chris Curvey writes: > > I have a database with the followin

Re: [GENERAL] Recursive CTE trees + Sorting by votes

On Thu, Aug 7, 2014 at 11:57 AM, Paul Jungwirth wrote: > > Or another idea, add a column that is the path of the parent: > > I don't think this will work. The problem is you need the full path to > keep the children with their parents, but you also need the score. If > you make the path an array

Re: [GENERAL] not finding rows using ctid

On 08/07/2014 12:40 PM, AI Rumman wrote: Hi, I am getting the logs as follows: LOG: process 32145 acquired ExclusiveLock on tuple (153420,5) of relation 663326 of database 475999 after 1123.028 ms But, when I am executing sqls to find the row on that table using the ctid = '(153420,5)', I g

Re: [GENERAL] dump/restore with a hidden dependency?

Chris Curvey-3 wrote >> -Original Message- >> From: Tom Lane [mailto: > tgl@.pa > ] >> Sent: Thursday, August 07, 2014 2:50 PM >> To: Chris Curvey >> Cc: > pgsql-general@ >> Subject: Re: [GENERAL] dump/restore with a hidden dependency? >> >> Chris Curvey < > ccurvey@ > > writes: >> >

Re: [GENERAL] not finding rows using ctid

I didn't execute any Vacuum Full and I tried to get the row after 3 hours of the issue. Thanks. On Thu, Aug 7, 2014 at 1:51 PM, Adrian Klaver wrote: > On 08/07/2014 12:40 PM, AI Rumman wrote: > >> >> Hi, >> >> I am getting the logs as follows: >> >> LOG: process 32145 acquired ExclusiveLock o

Re: [GENERAL] not finding rows using ctid

On 08/07/2014 02:14 PM, AI Rumman wrote: I didn't execute any Vacuum Full and I tried to get the row after 3 hours of the issue. Also, "...a row's ctid will change if it is updated..." Thanks. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] {xml}

Ramesh T wrote > Hello, > when i ran following query on postgres 9.3, > SELECT xmlagg(xmlelement(name e,part_id||',')) from part; > > result > .. > {xml} > > how to get part_id's..? please let me know .. > advance thanks, > R.. Are you using psql? What version of Pos

Re: [GENERAL] Re: Feature proposal and discussion: full-fledged column/function equivalence

David Johnston writes: > On Fri, Aug 1, 2014 at 6:22 PM, Chris Travers > wrote: >> On Fri, Aug 1, 2014 at 12:19 PM, David G Johnston < >> david.g.johns...@gmail.com> wrote: >>> More to the point: if you are writing a multiple-relation query and have >>> "testfunction" functions defined for at lea

Re: [GENERAL] dump/restore with a hidden dependency?

Chris Curvey wrote: > Perhaps a pair of triggers?  An insert-or-update trigger on foo, and a > delete-or-update trigger on bar? Using a foreign key constraint is best if that can do the right thing.  If that doesn't work, triggers like you describe are probably the best option, but you need to c

Re: [GENERAL] The dangers of streaming across versions of glibc: A cautionary tale

On Thu, Aug 7, 2014 at 9:46 AM, Bruce Momjian wrote: > We could walk the index looking for inconsistent btree splits, e.g. the > split doesn't match the ordering returned by the existing collation > functions. I'm not sure I follow. I don't think that a tool like my btreecheck tool will necessari

Re: [GENERAL] How to get PG 9.3 for a RaspberryPI (Debian Wheezy)?

Andreas writes: > When I checked the logfile of the local PG-9.3 server I found an error > on startup that complained it couldn't bind a IPv4 socket for 5432. > Another error says autovauum is not running. It would help if you provided the exact log messages, but what this sounds like is a netwo

Re: [GENERAL] postgresql referencing and creating types as record

In reply to this post by vpmm2007 create or replace package CUM_A_TYPES as type LT_PAYMENT is record (BASICnumber, DPnumber, GRADE_PAYnumber ); TYPE TYPE_CALC_TAX is record ( FIN_ROLE_ID number(8), CALC_FOR_ROLE_CODE number(4)); NEED TO CONVERT THIS TO POSTGRE