[GENERAL] Visual query builder for PosgreSQL?

Hi, I'm looking for some good visual query builder which can be used by non-tech people for some ETL tasks. Do you have any recommendation? Now, we're moving our data from Excel to PostgreSQL to deal with large amount of data, and we need to process some ETL tasks, with using JOIN and GROUP BY be

Re: [GENERAL] pl/java for postgresql 9.2

Hello 2013/2/7 Marc Brazeau : > Hoping someone can help me here. > > I'm trying to get pl/java running under Postgres 9.2. Doing so was > relatively easy on the Mac. But on Windows, its turning out to be quite > problematic. > > There are no recent binaries available, and if I try with the ones

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

REINDEX was for the whole database. It seems REINDEX was blocked by the <*idle in transaction*> process. What we are not able to explain is how that connection went in to <*idle in transaction*> state. The app stacktrace confirms that app (JDBC) is trying to open a connection. We do close connecti

Re: [GENERAL] Hot Standby has PANIC: WAL contains references to invalid pages

Hi, We suspect the problem is not in that area, because we used pg_basebackup on the same database pair under 9.1 and did not have any such problems. Looking at the context of the crashes, they seem to relate to handling of visibility maps during WAL replay. Going by the 9.2 release notes that

Re: [GENERAL] WARNING: pgstat wait timeout

Hi Dinesh, Thanks for that - it's when it's vacuuming a DB on Amazon EC2 so I think case 1 is probably the culprit. Jake On 5 February 2013 00:54, dinesh kumar wrote: > Hi, > > As of now, i found the following cases where we can expect these kind of > WARNING message in pg_log. > > Case 1 { H

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

On Thu, Feb 7, 2013 at 9:59 AM, Tom Lane wrote: > Geoff Winkless writes: > > On 7 February 2013 16:26, Tom Lane wrote: > >> Hmm, interesting hack. I guess that would meet the part of the spec > >> that says, eg, information_schema.constraint_column_usage.column_name > >> must be an identifier

[GENERAL] pl/java for postgresql 9.2

Hoping someone can help me here. I'm trying to get pl/java running under Postgres 9.2. Doing so was relatively easy on the Mac. But on Windows, its turning out to be quite problematic. There are no recent binaries available, and if I try with the ones on pgFoundry, I get the following error: ER

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Getting back to the OP (Andreas): On Tuesday, February 5, 2013 at 20:22, Andreas Joseph Krogh wrote: På tirsdag 05. februar 2013 kl. 09:59:54, skrev Albe Laurenz: Andreas Joseph Krogh wrote: ... Are there any plans to make NOT NULL constraints deferrable so one can avoid the trigger "boilerpla

Re: [GENERAL] best config

On Wed, Feb 6, 2013 at 1:04 PM, Steven Schlansker wrote: > > ok. we thought in this crossed-replication config because one heavy > query in one of the databases wouldnt affect the performance of the other. > > Both of your servers need to be powerful enough to handle the whole load, > otherwise yo

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Pavan Deolasee writes: > Sorry, I was going to ask what REINDEX was really indexing ? System > tables ? The stack trace for the REINDEX process includes ReindexDatabase(), so if it was running as a superuser it would be trying to reindex system catalogs too. We don't actually know that the parti

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Thu, Feb 7, 2013 at 8:19 PM, Anoop K wrote: > In an attempt to get access, I ended up killing a postgres process and the > whole thing recovered from hang state. Now don't have more data points to > debug. > Sorry, I was going to ask what REINDEX was really indexing ? System tables ? ISTM that

[GENERAL] Postgres 9.1 statistics in pg_stat_database

I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be accurate. What I'm after is to get proper transaction stats ie commits and rollbacks. Also I've no clu

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

Geoff Winkless writes: > On 7 February 2013 16:26, Tom Lane wrote: >> Hmm, interesting hack. I guess that would meet the part of the spec >> that says, eg, information_schema.constraint_column_usage.column_name >> must be an identifier --- at least if you also restricted which schema >> the func

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

On 7 February 2013 16:26, Tom Lane wrote: > Hmm, interesting hack. I guess that would meet the part of the spec > that says, eg, information_schema.constraint_column_usage.column_name > must be an identifier --- at least if you also restricted which schema > the function could be in. Apologies

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

Chris Travers writes: > On Thu, Feb 7, 2013 at 7:04 AM, Tom Lane wrote: >> Chris Travers writes: >>> What would be nice to be able to do is to be able to do something like: >>> ALTER TABLE inet_assignment ADD FOREIGN KEY (network(inet_address)) >>> REFERENCES cidr_block(block_def); >>> >>> 2. A

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

(Forgot to CC the list) On Thu, Feb 7, 2013 at 7:04 AM, Tom Lane wrote: > Chris Travers writes: > > What would be nice to be able to do is to be able to do something like: > > ALTER TABLE inet_assignment ADD FOREIGN KEY (network(inet_address)) > > REFERENCES cidr_block(block_def); > > > 2. Ar

Re: [GENERAL] 64 bit Win 2008, 32 bit client, ?bit Postgres?

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Russell Keane Sent: Thursday, February 07, 2013 9:25 AM To: pgsql-general@postgresql.org Subject: [GENERAL] 64 bit Win 2008, 32 bit client, ?bit Postgres? I appreciate this may have been answered b

Re: [GENERAL] configuring timezone

Terence, Thank you for the offer. But, I will probably be creating custom install scripts to run at destination location to modify parameter in Postgresql.conf. Regards, Igor Neyman From: Terence Ferraro [mailto:terencejferr...@gmail.com] Sent: Wednesday, February 06, 2013 6:47 PM To: Igor Neym

Re: [GENERAL] ERROR: invalid input syntax for integer: ""

On 02/06/2013 11:50 PM, Ben Madin wrote: Thank you to all for your help on this problem. I've summarised the resolution in the hope that it might help someone else. With all the advice I have gone forward and discovered that the issue related to a postcode anomaly. A client had provided a new

[GENERAL] 64 bit Win 2008, 32 bit client, ?bit Postgres?

I appreciate this may have been answered before but I can find no definitive answer so please bear with me... I found a good explanation here but it's a bit out of date: http://blog.hagander.net/archives/73-PostgreSQL-vs-64-bit-windows.html We have a 64 bit Win 2008 machine with 16 GB RAM Our 32

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

Chris Travers writes: > What would be nice to be able to do is to be able to do something like: > ALTER TABLE inet_assignment ADD FOREIGN KEY (network(inet_address)) > REFERENCES cidr_block(block_def); > 2. Are there any other major showstoppers I haven't thought of? The information_schema can'

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

In an attempt to get access, I ended up killing a postgres process and the whole thing *recovered from hang* state. Now don't have more data points to debug. I feel the trigger is the connection in <*idle in transaction>* state. On examining the application side(Java) stacktrace, I found that othe

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Jasen Betts wrote: > Well, the standard syntax allows them to be requested, check constraints too. > > what does the standard say about it behaviourally? What you'd expect: The checking of a constraint depends on its constraint mode within the current SQL-transaction. If the constraint mode is i

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Anoop K wrote: >I will try. Here are the gdb stacktraces of hung processes. > > > Have you tried `kill -SIGTERM` on the "idle in transaction" pid? -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/ma

Re: [GENERAL] Hot Standby has PANIC: WAL contains references to invalid pages

On Thu, Feb 7, 2013 at 7:39 AM, amutu wrote: > maybe pg_basebackup can`t handle such big database.try > rsync,pg_start_backup,rsync,pg_stop_backup,it always works fine for us.our > instance is about 2TB and we use pg9.1.x. It really should handle that without problem, but sure, it might be worth

[GENERAL] Re: [NOVICE] Problems with ñ and tildes / CSV import problems in PostgreSQL 9.1

On Wednesday, February 6, 2013 at 11:56, 2jt6w5k...@sneakemail.com (Zach Seaman znseaman-at-gmail.com |pg-gts/Basic|) wrote: This a similar question to this one , so I have encoded a database with LATIN-1 as sugge

Re: [GENERAL] Re: feature requests (possibly interested in working on this): functional foreign keys

On 7 February 2013 11:18, Thomas Kellerer wrote: > Geoff Winkless, 07.02.2013 11:46: > >> On 7 February 2013 09:38, Chris Travers > > wrote: >> >> 1: The foreign key depends on the function so the function cannot be >> dropped first absent CASCADE >> > [snip]

[GENERAL] pg_shdepend vacuum.

Hi All , Am using postgres version 9.0.1 . In daily postgres log am getting same kind of log for many time , is it because of more table creation and deletion is going on and its trying to vaccum pg_shdepend ? But its getting canceled every time , why is it so ? 2013-02-05 00:00:09 CST [25319]: [

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 2013-02-07, Albe Laurenz wrote: > Anyway, that's a sideline; at any rate the standard requires > deferrable NOT NULL constraints. Well, the standard syntax allows them to be requested, check constraints too. what does the standard say about it behaviourally? what do other major SQL databa

Re: [GENERAL] DEFERRABLE NOT NULL constraint

here's a relatively clean way to do circular references: given the circular reference: table a ( i serial primary key , j integer references b(j) deferrable initially deferred ); table b ( j serial primary key , i integer references a(i) ); t

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Wed, Feb 6, 2013 at 11:55 PM, Anoop K wrote: > We are hitting a situation where REINDEX is resulting in postgresql to go to > dead lock state for ever. On debugging the issue we found that > 3 connections are going in to some dead lock state. > > idle in transaction > REINDEX waiting > SELECT w

[GENERAL] Re: feature requests (possibly interested in working on this): functional foreign keys

Geoff Winkless, 07.02.2013 11:46: On 7 February 2013 09:38, Chris Travers mailto:chris.trav...@gmail.com>> wrote: 1: The foreign key depends on the function so the function cannot be dropped first absent CASCADE 2: If the function is redefined, one would have to check all rows to verify that t

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

I will try. Here are the gdb stacktraces of hung processes. #0 0x7fbdfaceb3e2 in recv () from /lib64/libc.so.6 #1 0x0058bde6 in secure_read () #2 0x0059697b in ?? () #3 0x00596d7b in pq_getbyte () #4 0x006334af in PostgresMain () #5 0x005f4d69 in

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Note that if those processes use persistent connections you'll need to restart them to free up the connections. In the meantime you can use the step of renaming your superuser account. Then cutting all superuser conns and turning off superuser of postgres user temporarily. I wouldn't do this all

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Processes should always connect by some other role with suspendable superuser connections for situations like this. Do your processes really need superuser access all the time? If you could turn it off for a bit you could get into your database and troubleshoot from there first. Not being able t

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Actually some of our processes connect as superuser. So even that is over and is in hung state. On Thu, Feb 7, 2013 at 4:29 PM, Scott Marlowe wrote: > So have you tried connecting as a superuser? > > On Thu, Feb 7, 2013 at 3:19 AM, Anoop K wrote: > > We did run out of conns as our processes whic

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

So have you tried connecting as a superuser? On Thu, Feb 7, 2013 at 3:19 AM, Anoop K wrote: > We did run out of conns as our processes which tried to connect (over few > days) got hung in 'startup waiting state'. Even superuser conns are also > over. > > Thanks > Anoop > > > On Thu, Feb 7, 2013 a

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Dean Rasheed wrote: >> ISO/IEC 9075-2:2003 says: >> >> Chapter 10.8 ( and ): >> >> ::= >> [ [ NOT ] DEFERRABLE ] >> | [ NOT ] DEFERRABLE [ ] >> >> ::= >> INITIALLY DEFERRED >> | INITIALLY IMMEDIATE >> >> >> So yes, the standard caters for deferrable NOT NULL constraints. >> >> Moreo

Re: [GENERAL] feature requests (possibly interested in working on this): functional foreign keys

On 7 February 2013 09:38, Chris Travers wrote: > 1: The foreign key depends on the function so the function cannot be > dropped first absent CASCADE > > 2: If the function is redefined, one would have to check all rows to > verify that they meet the new function's requirements. This could pose

[GENERAL] Re: "explain analyze" a procedure verbosely - to find which statement in it takes longer

This seems to have helped: # create index pref_cards_rid_index on pref_cards(rid); CREATE INDEX # \d pref_cards; Table "public.pref_cards" Column |Type | Modifiers -+-+--- rid | integer

[GENERAL] Re: "explain analyze" a procedure verbosely - to find which statement in it takes longer

Thank you for the replies - Using a shared library seemed too difficult for me at this moment, so I've prepended "explain analyze" to each query in my proc and the worst offender is this one: # explain analyze delete from pref_rounds r using temp_rids t where r.r

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

I dont have C triggers. I can attach gdb and get stacktrace. Wondering if it will take the processes out of problem state. Thanks Anoop On Thu, Feb 7, 2013 at 3:33 PM, Pavan Deolasee wrote: > On Thu, Feb 7, 2013 at 2:08 PM, Anoop K wrote: > > I have the setup in problem state. But I am not able

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

We did run out of conns as our processes which tried to connect (over few days) got hung in '*startup waiting state'. *Even superuser conns are also over. Thanks Anoop On Thu, Feb 7, 2013 at 3:37 PM, Scott Marlowe wrote: > It sounds like you're running out of connections. Have you tried > conne

Re: [GENERAL] Need help understanding WAL and checkpoints

Hi Albe --- On Wed, 2013/2/6, Albe Laurenz wrote: > drew_hunt wrote: > > I'm trying to get my head around WAL and checkpoints and need to ask a > > couple of questions before I > > get a headache. > > > > Firstly, I see the terms "WAL log", "WAL file" and "transaction log" all > > over the pl

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

It sounds like you're running out of connections. Have you tried connecting as postgres? It has 2 or 3 superuser connections reserved by default. On Thu, Feb 7, 2013 at 1:38 AM, Anoop K wrote: > I have the setup in problem state. But I am not able to make psql > connections to view the lock det

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

On Thu, Feb 7, 2013 at 2:08 PM, Anoop K wrote: > I have the setup in problem state. But I am not able to make psql > connections to view the lock details. > psql connections are hanging. Is there any other info which can be collected > in this state ? > Try attaching each process involved in the

[GENERAL] feature requests (possibly interested in working on this): functional foreign keys

Hi everyone; I have a project right now where functional foreign keys would be very handy. Let me explain the specific use case I am looking at and I would assume that there are many more. I am looking at building a network configuration database for virtualized machines in a hosting environment

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 7 February 2013 08:50, Dean Rasheed wrote: > That's actually a sensible default, because there are consequences to > making a constraint deferrable --- it can hurt performance if a large > number of rows need to be queued up for later checking... Just to clarify --- PostgreSQL goes to some eff

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > This restriction is specified in the SQL standard. Thanks! This is the kind of thing one CAN sell to customers :) "Your thing is out of standards, Sir" sounds much better than "But I really hate that, Sir". Which has, however, a terrible impact on the ORM that use circular FKs. Will have to

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 7 February 2013 09:02, Bèrto ëd Sèra wrote: > Hi > >> also a >> deferrable primary key/unique constraint can't be used as the target >> for a foreign key. > > ehr, why? I mean, I'm positive it cannot be used before an actual > value is in the record, but what would be the problem, apart from >

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > also a > deferrable primary key/unique constraint can't be used as the target > for a foreign key. ehr, why? I mean, I'm positive it cannot be used before an actual value is in the record, but what would be the problem, apart from that? Cheers Bèrto -- == If Pa

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 7 February 2013 07:45, Albe Laurenz wrote: > Gavan Schneider wrote: >> Taking a different tangent ... > > Good idea. > >> Is there anything in the SQL standards about NOT NULL >> constraints being deferrable? >> >> To my mind we should not consider implementing non-standard >> behaviour, but if

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

I have the setup in problem state. But I am not able to make psql connections to view the lock details. psql connections are hanging. Is there any other info which can be collected in this state ? Also we don't know the steps to reproduce the issue. On Thu, Feb 7, 2013 at 1:23 PM, Albe Laurenz w

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi all; I have some thoughts on this and I think deferrable not null constraints make some sense (and I think once one gets there deferrable check constraints make some sense too). My view of the use cases though are a bit different and assume thick clients where some data may be looked up and we

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Forgot to cc general On Tue, Feb 5, 2013 at 1:39 AM, Darren Duncan wrote: > Deferrable foreign key and unique key constraints I can understand, but ... > > > On 2013.02.05 1:22 AM, Andreas Joseph Krogh wrote: > >> +100 for having NOT NULL and CHECK-constraints deferrable:-) >> Is there any "I w