Re: [GENERAL] Immutable functions, Exceptions and the Query Optimizer

Cochise Ruhulessin wrote: > If an immutable function raises an exception, is that exception cached by the > query optimizer? Or does > it only cache in the case that a function actually returns a value? If an error occurs, query processing is terminated, so nothing needs to be cached. PostgreSQL

Re: [GENERAL] 9.2 RHEL6 yum Repository broken? (SOLVED...but how?)

Hello all. As a final postscript to this issue. I tried downloading the file using a server in our DMZ (direct net connection) and that worked. I therefore contacted our network guys, explained the situation and they are contacting the vendor who supplies our proxy. While the vendor hasn't

[GENERAL] Reset permissions on table

Hi By default there is no permissions on table (\dp return 0 row) If I make a GRANT, doing a REVOKE will not get me in the 'default' state How to return in the default state, where permission are 'inherited' from owner. Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@p

Re: [GENERAL] Reset permissions on table

etienne champetier wrote: > By default there is no permissions on table (\dp return 0 row) > If I make a GRANT, doing a REVOKE will not get me in the 'default' state > > How to return in the default state, where permission are 'inherited' from > owner. \dp should return a row for each table in y

Re: [GENERAL] pg_upgrade

I don't think it would get any further... It fails and --retain says "Retain sql and log files after success" I can look at that log file and all it indicates is failure to start the server. Maybe I should rephrase the question: pg_ctl returns failure, even though it starts the server, when run

Re: [GENERAL] Reset permissions on table

- Mail original - > De: "Albe Laurenz" > À: "etienne.champet...@free.fr *EXTERN*" , > pgsql-general@postgresql.org > Envoyé: Vendredi 15 Février 2013 13:25:14 > Objet: RE: [GENERAL] Reset permissions on table > > etienne champetier wrote: > > By default there is no permissions on table

Re: [GENERAL] Reset permissions on table

etienne champetier wrote: >>> By default there is no permissions on table (\dp return 0 row) >>> If I make a GRANT, doing a REVOKE will not get me in the 'default' >>> state >>> >>> How to return in the default state, where permission are >>> 'inherited' from owner. >> >> \dp should return a row fo

Re: [GENERAL] Query becomes slow when written as view

is your function stable/immutable, and if so is it decorated as such. No, itŽs volatile. Well, that's your problem. The planner won't push down the IN clause past the volatile function for fear of changing the query's side-effects. I'd question whether it's sane to have a view with volatile

Re: [GENERAL] bug, bad memory, or bad disk?

On Friday, February 15, 2013 1:33 AM Ben Chobot wrote: > 2013-02-13T23:13:18.042875+00:00 pgdb18-vpc postgres[20555]: [76-1]  ERROR:  invalid memory alloc request size > 1968078400 > 2013-02-13T23:13:18.956173+00:00 pgdb18-vpc postgres[23880]: [58-1]  ERROR:  invalid page header in block 2948 of

Re: [GENERAL] bug, bad memory, or bad disk?

On Fri, Feb 15, 2013 at 8:08 AM, Amit Kapila wrote: > On Friday, February 15, 2013 1:33 AM Ben Chobot wrote: > >> 2013-02-13T23:13:18.042875+00:00 pgdb18-vpc postgres[20555]: [76-1] > ERROR: invalid memory alloc request size >> 1968078400 >> 2013-02-13T23:13:18.956173+00:00 pgdb18-vpc postgres[2

Re: [GENERAL] bug, bad memory, or bad disk?

> -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: Friday, February 15, 2013 7:56 PM > To: Amit Kapila > Cc: Ben Chobot; PostgreSQL General > Subject: Re: [GENERAL] bug, bad memory, or bad disk? > > On Fri, Feb 15, 2013 at 8:08 AM, Amit Kapila > wrote: > > O

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

Joe Van Dyk wrote: Perhaps I fat-fingered something somewhere... I tried that and I got this: https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt The with_filters view uses a different plan. Interesting. It is avoiding the hash join, but it

Re: [GENERAL] Reset permissions on table

On 02/15/2013 05:02 AM, etienne.champet...@free.fr wrote: If i do a GRANT and a REVOKE, i loose access to the table. \dp return the same thing but before GRANT it's NULL and after REVOKE it's not. It matter when you want to change owner and you forgot this table where there is explicit r

Re: [GENERAL] pg_upgrade

On 02/15/2013 04:54 AM, Ian Harding wrote: I don't think it would get any further... It fails and --retain says "Retain sql and log files after success" I can look at that log file and all it indicates is failure to start the server. What is the exact error message from the log? Maybe I sh

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

Joe Van Dyk writes: > Perhaps I fat-fingered something somewhere... I tried that and I got this: > https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt Try without the useless "is true" bits. regards, tom lane -- Sent via

[GENERAL] Case insensitive hstore.

Hi Does anyone know how one would select from a table with a hstore field treating the key of the hstore as case insensitive. ie SELECT id, lower(additional_info->'type') AS type FROM table I would like this to work even if if the store tyoe is 'Type' -> 'original' failing that is there a wa

Re: [GENERAL] How to get stored procedure args list from metadata tables ?

Is there anything like this soln for v8.3.4 ? I know, I know, just upgrade. But its out of my hands. -Original Message- From: Pavel Stehule [mailto:pavel.steh...@gmail.com] Sent: Wednesday, February 13, 2013 9:24 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENER

Re: [GENERAL] pg_upgrade

On Fri, Feb 15, 2013 at 4:54 AM, Ian Harding wrote: > I don't think it would get any further... It fails and --retain says > "Retain sql and log files after success" I can look at that log file and > all it indicates is failure to start the server. > > Maybe I should rephrase the question: pg_c

[GENERAL] could not link file "pg_xlog/xlogtemp.72606"

Hi there, We recently copied over our 8.3 database cluster files from a local disk to a network attached storage drive. Everything seemed to work okay for a while until we got this error: ERROR: could not link file "pg_xlog/000100AA0030" to "pg_xlog/000100AA0096"

Re: [GENERAL] pg_upgrade

- pg_upgrade run on Fri Feb 15 05:09:34 2013 - command: "/usr/lib/postgresql/8.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/8.4/main/" -o "-p 504

Re: [GENERAL] pg_upgrade

This is interesting, although I'm not sure it's relevant. From pg_ctl source. 00477 write_stderr (_ ("\n%s: -w option is not su

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

On Fri, Feb 15, 2013 at 7:43 AM, Tom Lane wrote: > Joe Van Dyk writes: > > Perhaps I fat-fingered something somewhere... I tried that and I got > this: > > > https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt > > Try without the useless "is true"

Re: [GENERAL] pg_upgrade

Maybe this is it. 8.4 pg_ctl docs say it uses "psql -l" to see if it's finished when you use -w. It also says PGPORT Default port for psql(used by the -w option). And since pg_upgrade uses a funky port, it might miss unless the PGPORT env

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

Joe Van Dyk writes: > On Fri, Feb 15, 2013 at 7:43 AM, Tom Lane wrote: >> Try without the useless "is true" bits. > Huh, that did do the trick. Why does "is true" affect the plan? Because "(x = y) IS TRUE" isn't the same as "x = y". (The behavior for nulls is different.) And the planner only

Re: [GENERAL] Avoiding duplication of code via views -- slower? How do people typically do this?

On Fri, Feb 15, 2013 at 1:42 AM, Chris Travers wrote: > On Thu, Feb 14, 2013 at 4:32 PM, Joe Van Dyk wrote: >> >> See >> https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txt >> for the code. >> >> I have promotions(id, end_at, quantity) and >> promo

[GENERAL] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

Howdy! This query is coming from PgPool I believe. SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'import_jobs' AND c.relpersistence = 'u' This is a very small database, like 10/15 tables, it's basically empty. If i run this query manually, it comes back immediatly. However

Re: [GENERAL] Case insensitive hstore.

2013/2/16 Glenn Pierce : > Hi > > Does anyone know how one would > select from a table with a hstore field treating the key of the hstore as > case insensitive. > > ie > > SELECT id, lower(additional_info->'type') AS type FROM table > > I would like this to work even if if the store tyoe is > > 'Ty

Re: [GENERAL] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

I can confirm it comes from pgpool, and is very weird , I guess the pgpool list would be a better fit. Cheers. On Friday, February 15, 2013 01:58:55 PM David Kerr wrote: > Howdy! > > This query is coming from PgPool I believe. > > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =

Re: [GENERAL] How to create trigger if it does not exist

Hi Guys, Using CASCADE can be a bit dangerous as there might be other tables, functions, views etc. that will be dropped but they are not meant to be. Try this: DO $$ BEGIN IF NOT EXISTS(SELECT * FROM information_schema.triggers WHERE event_object_tabl

Re: [GENERAL] Visual query builder for PosgreSQL?

(2013/02/08 20:37), Russell Keane wrote: 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 ET

Re: [GENERAL] Visual query builder for PosgreSQL?

(2013/02/09 0:41), Adrian Klaver wrote: On 02/08/2013 07:33 AM, Kevin Grittner wrote: Satoshi Nagayasu wrote: Of course, I can write ad-hoc queries by myself. However, I'd like to allow non-tech people to issue ad-hoc queries with using some visual query builder. You should probably take a

Re: [GENERAL] pg_upgrade

On Fri, Feb 15, 2013 at 10:36:25AM -0800, Ian Harding wrote: > Maybe this is it.  8.4 pg_ctl docs say it uses "psql -l" to see if it's > finished when you use -w.  It also says >   > PGPORT > > Default port for psql (used by the -w option). > > And since pg_upgrade uses a funky port, it m

Re: [GENERAL] PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...

On Fri, Feb 15, 2013 at 07:01:20PM -0600, Ren Romero Benavides wrote: - I can confirm it comes from pgpool, and is very weird , I guess the pgpool list - would be a better fit. - Cheers. hmm, ok thanks I'll post there as well. Also, if anyone else stumbles upon this, it only seems to happen wit