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

[Albe Laurenz]

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 doesn't cache function results, immutable
or not:

CREATE OR REPLACE FUNCTION i(integer) RETURNS integer
   LANGUAGE plpgsql IMMUTABLE STRICT AS
$$BEGIN
   RAISE NOTICE 'Called for %', $1;
   RETURN $1;
END$$;

WITH t(t) AS (VALUES (1), (2), (1))
   SELECT i(t) FROM t;

NOTICE:  Called for 1
NOTICE:  Called for 2
NOTICE:  Called for 1
 i
---
 1
 2
 1
(3 rows)

The difference is that an immutable function, when applied
to a constant, can be evaluated at query planning time:

WITH t(t) AS (VALUES (1), (2), (1))
   SELECT i(42) FROM t;

NOTICE:  Called for 42
 i

 42
 42
 42
(3 rows)

Notice that the function was evaluated only once.

> The use case is a table books(book_id NOT NULL PRIMARY KEY, type_id) wherein 
> type_id is considered
> immutable (enforced with a trigger).

No database object is immutable (note that "immutable"
means something else here than in the case of a function,
so don't mix those up).

You can, for example, drop the table.

Any function that SELECTs from the database cannot
be immutable.

> The function f() must return type_id given book_id, and raise an exception if 
> no entity with book_id
> exists. I'd like this function to be immutable so it can be used as a check 
> constraint.

The documentation says in
http://www.postgresql.org/docs/current/static/sql-createtable.html

  Currently, CHECK expressions cannot contain subqueries nor
  refer to variables other than columns of the current row.

CHECK constraints are only verified when the value is modified,
so nothing can prevent the constraint from getting violated
after the row has been added.

It might, for example, lead to problems during dump/restore,
as seen here:
http://www.postgresql.org/message-id/29488.1332857...@sss.pgh.pa.us

What should the CHECK constraint achieve?
Maybe it can be expressed with a BEFORE trigger or some
other construct.

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jeffrey Jones]

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 replied yet the download has started magically 
working again so I am assuming they have fixed it and haven't gotten 
back to us yet.


At least I hope that is the case, this suddenly stopped working and I 
would be worried if it suddenly started working again, ah the joys of 
having a non-deterministic network.


Thanks for all the help.

Regards,

Jeff

On 15/02/13 10:52, Stephen Frost wrote:

* Jeffrey Jones (jjo...@toppan-f.co.jp) wrote:

I downloaded 
http://yum.postgresql.org/9.2/redhat/rhel-6Server-x86_64/repodata/primary.sqlite.bz2
using wget on the afected computer and ran md5sum over it with the
following result:

9258bd5672cf7abb55a0d95ee2467afc  primary.sqlite.bz2

That's pretty cute.  I get:

sfrost@tamriel:/home/sfrost> md5sum primary.sqlite.bz2
168232945d791e55e56bb29757458157  primary.sqlite.bz2

Which matches an md5sum run on the file on the server directly and the
file timestamp indicates that it hasn't changed for ~2 days.

Have you tried opening the file you end up receiving and inspecting it?
Something is clearly off if you're getting a different md5sum.


I have downloaded it from a few separate computers and they all
return that same result.

Can you try from a system that isn't attached to your corporate network?


I also extracted and looked at the sqlite file  with sqliteman and
it worked without trouble. If there really was a problem then I
would have expected sqliteman to fail reading the file.

Agreed, that's curious- perhaps there's a cacheing transparent proxy in
the corporate network that's feeding you an older, but otherwise valid,
file?

Thanks,

Stephen




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Reset permissions on table

[etienne . champetier]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Reset permissions on table

[Albe Laurenz]

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
your search_path.

I assume that you are referring to the empty
"Access privileges" column.

After granting and revoking a privilege, the value
showd be "owner=arwdDxt/owner" (where "owner" is
the user that owns the table).

This is the default value: the owner has all
privileges and nobody else has any.

It does not matter if the ACL is left empty
(it contains a NULL value initially) or if
it contains the default explicitly.

Yours,
Laurenz Albe



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade

[Ian Harding]

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 with the -w flag.


On Thu, Feb 14, 2013 at 10:41 PM, Ian Lawrence Barwick wrote:

> 2013/2/15 Ian Harding 
> >
> >
> > On Feb 14, 2013, at 9:50 PM, Ian Lawrence Barwick 
> wrote:
> >
> > 2013/2/15 Ian Harding 
> >>
> >> When I run pg_upgrade, it tries to start the old cluster with the -w
> flag, which waits a while and declares failure, even though it starts the
> server.  If I start/stop without -w everything is great.
> >>
> >> Can I tell pg_upgrade not to use that flag, or is there a reason it is
> not working that I should look into?
> >>
> >> version
> >>
> --
> >>  PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
> (Debian 4.4.5-8) 4.4.5, 64-bit
> >>
> >
> > Which PostgreSQL version is the old cluster, and which version is the
> new cluster? What options are you supplying to pg_upgrade, and what output
> are you getting?
> >
>
> > Old is 8.4, new is 9.2.  I am not supplying an but the minimum options
> and --check succeeds.  My
> > pg_ctl fails when run by hand with -w (although the database does start)
> so I know that's the issue.
>
> Maybe try running pg_upgrade with the  --retain option and check
> pg_upgrade_server.log for clues?
>
>
> Ian Barwick
>

Re: [GENERAL] Reset permissions on table

[etienne . champetier]

- 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 (\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
> your search_path.

Sorry, i meant \dp  and it effectively return 1 row per table 
(mistype ...)

> 
> I assume that you are referring to the empty
> "Access privileges" column.
> 
> After granting and revoking a privilege, the value
> showd be "owner=arwdDxt/owner" (where "owner" is
> the user that owns the table).
> 
> This is the default value: the owner has all
> privileges and nobody else has any.
> 
> It does not matter if the ACL is left empty
> (it contains a NULL value initially) or if
> it contains the default explicitly.

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 right.

> 
> Yours,
> Laurenz Albe
> 
Thanks for your quick answer


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Reset permissions on table

[Albe Laurenz]

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
>> your search_path.
> 
> Sorry, i meant \dp  and it effectively return 1 row per table 
> (mistype ...)
> 
>> I assume that you are referring to the empty
>> "Access privileges" column.
>>
>> After granting and revoking a privilege, the value
>> showd be "owner=arwdDxt/owner" (where "owner" is
>> the user that owns the table).
>>
>> This is the default value: the owner has all
>> privileges and nobody else has any.
>>
>> It does not matter if the ACL is left empty
>> (it contains a NULL value initially) or if
>> it contains the default explicitly.
> 
> 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 right.

I don't understand - it seems to work for me:

CREATE TABLE test(id integer PRIMARY KEY);

\dp test
   Access privileges
 Schema  | Name | Type  | Access privileges | Column access privileges
-+--+---+---+--
 laurenz | test | table |   |
(1 row)

REVOKE SELECT ON test FROM georg;

\dp test
  Access privileges
 Schema  | Name | Type  |Access privileges| Column access privileges
-+--+---+-+--
 laurenz | test | table | laurenz=arwdDxt/laurenz |
(1 row)

These are the default privileges.

ALTER TABLE test OWNER TO georg;

\dp test
Access privileges
 Schema  | Name | Type  |  Access privileges  | Column access privileges
-+--+---+-+--
 laurenz | test | table | georg=arwdDxt/georg |
(1 row)

The table still has the default privileges,
but now it belongs to "georg".

Maybe you can illustrate your problem with an example.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jan Strube]

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 functions in
it at all.  It certainly won't act much like the normal understanding of
a view ...


I see, thanks for the explanation.
In this case, the side effect is desired. The view should always return 
a COMMENT. Either directly from one of the tables or generated from the 
function which stores the COMMENT in cached_comments for the next select.
Is there perhaps a best practice to do a thing like that? Of course we 
could declare the original function stable and call another volatile 
function to store the data, as noted in the docs. But that would be 
cheating...


Regards,
Jan



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Amit Kapila]

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 
> relation pg_tblspc/16435/PG_9.1_201105231/188417/56951641
> 2013-02-13T23:13:19.025971+00:00 pgdb18-vpc postgres[25027]: [36-1]
 ERROR:  could not open file 
> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
3936767042): No such file or directory
> 2013-02-13T23:13:19.847422+00:00 pgdb18-vpc postgres[28333]: [8-1]  ERROR:
 could not open file 
> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
3936767042): No such file or directory
> 2013-02-13T23:13:19.913595+00:00 pgdb18-vpc postgres[28894]: [8-1]  ERROR:
 could not open file 
> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
3936767042): No such file or directory
> 2013-02-13T23:13:20.043527+00:00 pgdb18-vpc postgres[20917]: [72-1]
 ERROR:  invalid memory alloc request size 
> 1968078400
> 2013-02-13T23:13:21.548259+00:00 pgdb18-vpc postgres[23318]: [54-1]
 ERROR:  could not open file 
> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
3936767042): No such file or directory
> 2013-02-13T23:13:28.405529+00:00 pgdb18-vpc postgres[28055]: [12-1]
 ERROR:  invalid page header in block 38887 of 
> relation pg_tblspc/16435/PG_9.1_201105231/188417/58206627
> 2013-02-13T23:13:29.199447+00:00 pgdb18-vpc postgres[25513]: [46-1]
 ERROR:  invalid page header in block 2368 of 
> relation pg_tblspc/16435/PG_9.1_201105231/188417/60418945

> There didn't seem to be much correlation to which files were affected, and
this was a critical server, so once we 
> realized a simple reindex wasn't going to solve things, we shut it down
and brought up a slave as the new master db.

> While that seemed to fix these issues, we soon noticed problems with
missing clog files. The missing clogs were outside > the range of the
existing clogs, so we tried using dummy clog files. It didn't help, and
running pg_check we found that > one block of one table was definitely
corrupt. Worse, that corruption had spread to all our replicas.

Can you check that corrupted block is from one of the relations mentioned in
your errors. This is just to reconfirm.

> I know this is a little sparse on details, but my questions are:

> 1. What kind of fault should I be looking to fix? Because it spread to all
the replicas, both those that stream and 
> those that replicate by replaying wals in the wal archive, I assume it's
not a storage issue. (My understanding is that > streaming replicas stream
their changes from memory, not from wals.) 

  Streaming replication stream their changes from wals.

> 2. Is it possible that the corruption that was on the master got
replicated to the slaves when I tried to cleanly shut > down the master
before bringing up a new slave as the new master and switching the other
slaves over to replicating 
> from that?

At shutdown, master will send all WAL (upto shutdown checkpoint)

I think there are 2 issues in your mail
1. access to corrupted blocks - there are 2 things in this, one is how the
block get corrupted in master and why it's replicated to other servers.

The corrupted block replication can be done because of WAL as WAL contains
backup copies of blocks if full_page_write=on, which is default
configuration.
So I think now the main question remains is how the block/'s get corrupted
on master. For that I think some more information is required, like what
kind of operations are being done for relation which has corrupted block.
If we drop and recreate that relation, will this problem remains.
Is there any chance that the block gets corrupted due to hardware problem?

2. missing clog files - how did you find missing clog files, is any
operation got failed or just an observation?
Do you see any problems in system due to it?

With Regards,
Amit Kapila.





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Merlin Moncure]

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[23880]: [58-1]
>  ERROR:  invalid page header in block 2948 of
>> relation pg_tblspc/16435/PG_9.1_201105231/188417/56951641
>> 2013-02-13T23:13:19.025971+00:00 pgdb18-vpc postgres[25027]: [36-1]
>  ERROR:  could not open file
>> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> 3936767042): No such file or directory
>> 2013-02-13T23:13:19.847422+00:00 pgdb18-vpc postgres[28333]: [8-1]  ERROR:
>  could not open file
>> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> 3936767042): No such file or directory
>> 2013-02-13T23:13:19.913595+00:00 pgdb18-vpc postgres[28894]: [8-1]  ERROR:
>  could not open file
>> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> 3936767042): No such file or directory
>> 2013-02-13T23:13:20.043527+00:00 pgdb18-vpc postgres[20917]: [72-1]
>  ERROR:  invalid memory alloc request size
>> 1968078400
>> 2013-02-13T23:13:21.548259+00:00 pgdb18-vpc postgres[23318]: [54-1]
>  ERROR:  could not open file
>> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> 3936767042): No such file or directory
>> 2013-02-13T23:13:28.405529+00:00 pgdb18-vpc postgres[28055]: [12-1]
>  ERROR:  invalid page header in block 38887 of
>> relation pg_tblspc/16435/PG_9.1_201105231/188417/58206627
>> 2013-02-13T23:13:29.199447+00:00 pgdb18-vpc postgres[25513]: [46-1]
>  ERROR:  invalid page header in block 2368 of
>> relation pg_tblspc/16435/PG_9.1_201105231/188417/60418945
>
>> There didn't seem to be much correlation to which files were affected, and
> this was a critical server, so once we
>> realized a simple reindex wasn't going to solve things, we shut it down
> and brought up a slave as the new master db.
>
>> While that seemed to fix these issues, we soon noticed problems with
> missing clog files. The missing clogs were outside > the range of the
> existing clogs, so we tried using dummy clog files. It didn't help, and
> running pg_check we found that > one block of one table was definitely
> corrupt. Worse, that corruption had spread to all our replicas.
>
> Can you check that corrupted block is from one of the relations mentioned in
> your errors. This is just to reconfirm.
>
>> I know this is a little sparse on details, but my questions are:
>
>> 1. What kind of fault should I be looking to fix? Because it spread to all
> the replicas, both those that stream and
>> those that replicate by replaying wals in the wal archive, I assume it's
> not a storage issue. (My understanding is that > streaming replicas stream
> their changes from memory, not from wals.)
>
>   Streaming replication stream their changes from wals.

Yeah.  This smells like disk corruption to me, but it really could be
anything.  Unfortunately it can spread to the replicas especially if
you're not timely about taking the master down.  page checksums (a
proposed feature) are a way of dealing with this problem.

The biggest issue is the missing clog files -- did you have more than
one replica? Were they missing on all of them?

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Amit Kapila]

> -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:
> > 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
> >> relation pg_tblspc/16435/PG_9.1_201105231/188417/56951641
> >> 2013-02-13T23:13:19.025971+00:00 pgdb18-vpc postgres[25027]: [36-1]
> >  ERROR:  could not open file
> >> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> > 3936767042): No such file or directory
> >> 2013-02-13T23:13:19.847422+00:00 pgdb18-vpc postgres[28333]: [8-1]
> ERROR:
> >  could not open file
> >> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> > 3936767042): No such file or directory
> >> 2013-02-13T23:13:19.913595+00:00 pgdb18-vpc postgres[28894]: [8-1]
> ERROR:
> >  could not open file
> >> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> > 3936767042): No such file or directory
> >> 2013-02-13T23:13:20.043527+00:00 pgdb18-vpc postgres[20917]: [72-1]
> >  ERROR:  invalid memory alloc request size
> >> 1968078400
> >> 2013-02-13T23:13:21.548259+00:00 pgdb18-vpc postgres[23318]: [54-1]
> >  ERROR:  could not open file
> >> "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1" (target block
> > 3936767042): No such file or directory
> >> 2013-02-13T23:13:28.405529+00:00 pgdb18-vpc postgres[28055]: [12-1]
> >  ERROR:  invalid page header in block 38887 of
> >> relation pg_tblspc/16435/PG_9.1_201105231/188417/58206627
> >> 2013-02-13T23:13:29.199447+00:00 pgdb18-vpc postgres[25513]: [46-1]
> >  ERROR:  invalid page header in block 2368 of
> >> relation pg_tblspc/16435/PG_9.1_201105231/188417/60418945
> >
> >> There didn't seem to be much correlation to which files were
> affected, and
> > this was a critical server, so once we
> >> realized a simple reindex wasn't going to solve things, we shut it
> down
> > and brought up a slave as the new master db.
> >
> >> While that seemed to fix these issues, we soon noticed problems with
> > missing clog files. The missing clogs were outside > the range of the
> > existing clogs, so we tried using dummy clog files. It didn't help,
> and
> > running pg_check we found that > one block of one table was
> definitely
> > corrupt. Worse, that corruption had spread to all our replicas.
> >
> > Can you check that corrupted block is from one of the relations
> mentioned in
> > your errors. This is just to reconfirm.
> >
> >> I know this is a little sparse on details, but my questions are:
> >
> >> 1. What kind of fault should I be looking to fix? Because it spread
> to all
> > the replicas, both those that stream and
> >> those that replicate by replaying wals in the wal archive, I assume
> it's
> > not a storage issue. (My understanding is that > streaming replicas
> stream
> > their changes from memory, not from wals.)
> >
> >   Streaming replication stream their changes from wals.
> 
> Yeah.  This smells like disk corruption to me, but it really could be
> anything.  Unfortunately it can spread to the replicas especially if
> you're not timely about taking the master down.  page checksums (a
> proposed feature) are a way of dealing with this problem.

Yes, it can be one of the reason. 

One more thing I observed is the block it is trying to access in file has
very large block number (Ex. See in below log message). Is it really
feasible to have such large block number?

2013-02-13T23:13:19.025971+00:00 pgdb18-vpc postgres[25027]: [36-1]  ERROR:
could not open file "pg_tblspc/16435/PG_9.1_201105231/188417/58206627.1"
(target block 3936767042): No such file or directory

Also the first error message:
"invalid memory alloc request size 1968078400"
Here why and which operation requests such a memory request?

Not sure if there is any chances of memory corruption?

> The biggest issue is the missing clog files -- did you have more than
> one replica? Were they missing on all of them?

Won't it be possible that there are less clog files on replica. Not sure if
such can happen in 
Normal scenario's?

With Regards,
Amit Kapila.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jack Christensen]

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 is still evaluating 
the exists column even when it is not referenced at all in the select. I 
would have expected the optimizer to remove it entirely.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Reset permissions on table

[Adrian Klaver]

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 right.


If it is a matter of finding who the table owner is then do:

\dt table_name






Yours,
Laurenz Albe


Thanks for your quick answer





--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade

[Adrian Klaver]

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 should rephrase the question:  pg_ctl returns failure, even
though it starts the server, when run with the -w flag.





--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

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 pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Case insensitive hstore.

[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

'Type' -> 'original'


failing that is there a way to lowercase the keys and values of the hstore
field of the entire table ?

Thanks

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

[Gauthier, Dave]

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: [GENERAL] How to get stored procedure args list from metadata 
tables ?

Hello

2013/2/13 Gauthier, Dave :
> Hi:
>
>
>
> Looking for a table or view which contains the list of arguments that 
> are passed to a stored procedure.  Doesn't seem to be in 
> pg_proc.prosrc or other pg_proc columns.
>

It is in pg_proc - argument's description is stored in combination of
fields: pronargs, pronargdefaults, proargtypes, proallargtypes, proargmodes, 
proargnames, proargdefaults

system function pg_get_function_arguments can returns usual description



postgres=# select pg_get_function_arguments('check_form_rodne_cislo'::regproc);
 pg_get_function_arguments

 character varying, boolean
(1 row)

regards

Pavel Stehule

>
>
> Thanks in Advance for any help.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade

[Jeff Janes]

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_ctl returns failure, even though
> it starts the server, when run with the -w flag.

How long does the server take to start?

If it takes the server more than 1 minute to start, then what you
describe is expected.

Cheers,

Jeff


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Claire McLister]

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" (initialization of log file 170, segment 
150): Operation not supported


 pg_xlog/000100AA0096 does not exist in the original file tree, so 
I'm wondering if there's some special link that PG is trying to create that is 
not permitted in the NAS drive?


 This is for Postgresql 8.3 + PostGIS 1.5


 Would appreciate any pointers. This is a test environment, so nothing is lost, 
but we'd like to use the NAS for storage as the local disk doesn't have enough 
space.


Claire.

Re: [GENERAL] pg_upgrade

[Ian Harding]

-
  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 50432 -c autovacuum=off -c
autovacuum_freeze_max_age=20 -c listen_addresses='' -c
unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
waiting for server to
start...could
not start server


The funny thing is, the server starts instantly.


On Fri, Feb 15, 2013 at 9:19 AM, Jeff Janes  wrote:

> 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_ctl returns failure, even
> though
> > it starts the server, when run with the -w flag.
>
> How long does the server take to start?
>
> If it takes the server more than 1 minute to start, then what you
> describe is expected.
>
> Cheers,
>
> Jeff
>

Re: [GENERAL] pg_upgrade

[Ian Harding]

This is interesting, although I'm not sure it's relevant.  From pg_ctl
source.


00477
write_stderr
(_
("\n%s: -w option is not supported when starting a pre-9.1 server\n"),


On Fri, Feb 15, 2013 at 9:45 AM, Ian Harding  wrote:

>
> -
>   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 50432 -c
> autovacuum=off -c autovacuum_freeze_max_age=20 -c
> listen_addresses='' -c unix_socket_permissions=0700" start >>
> "pg_upgrade_server.log" 2>&1
> waiting for server to
> start...could
> not start server
>
>
> The funny thing is, the server starts instantly.
>
>
> On Fri, Feb 15, 2013 at 9:19 AM, Jeff Janes  wrote:
>
>> 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_ctl returns failure, even
>> though
>> > it starts the server, when run with the -w flag.
>>
>> How long does the server take to start?
>>
>> If it takes the server more than 1 minute to start, then what you
>> describe is expected.
>>
>> Cheers,
>>
>> Jeff
>>
>
>

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

[Joe Van Dyk]

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" bits.
>
> regards, tom lane
>


Huh, that did do the trick. Why does "is true" affect the plan?

without "is true" in the conditions:
--
 Aggregate  (cost=16676.66..16676.67 rows=1 width=0) (actual time=
95.648..95.648 rows=1 loops=1)
   ->  Bitmap Heap Scan on promotions p  (cost=868.37..16619.49 rows=22868
width=0) (actual time=11.031..95.294 rows=2720 loops=1)
 Recheck Cond: (end_at > (now() - '30 days'::interval))
 Filter: ((quantity = 1) AND (SubPlan 1))
 Rows Removed by Filter: 43073
 ->  Bitmap Index Scan on index_promotions_on_end_at
 (cost=0.00..862.65 rows=46093 width=0) (actual time=10.783..10.783
rows=73234 loops=1)
   Index Cond: (end_at > (now() - '30 days'::interval))
 SubPlan 1
   ->  Index Only Scan using index_promotion_usages_on_promotion_id
on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual
time=0.001..0.001 rows=0 loops=44998)
 Index Cond: (promotion_id = p.id)
 Heap Fetches: 2720
 Total runtime: 95.739 ms
(12 rows)


with "is true" in the conditions:

  QUERY PLAN

--
 Aggregate  (cost=94430.93..94430.94 rows=1 width=0) (actual
time=534.568..534.569 rows=1 loops=1)
   ->  Seq Scan on promotions p  (cost=0.00..94373.76 rows=22868 width=0)
(actual time=0.306..534.165 rows=2720 loops=1)
 Filter: (((quantity = 1) IS TRUE) AND ((end_at > (now() - '30
days'::interval)) IS TRUE) AND ((SubPlan 1) IS TRUE))
 Rows Removed by Filter: 600105
 SubPlan 1
   ->  Index Only Scan using index_promotion_usages_on_promotion_id
on promotion_usages pu  (cost=0.00..20.54 rows=178 width=0) (actual
time=0.001..0.001 rows=0 loops=44998)
 Index Cond: (promotion_id = p.id)
 Heap Fetches: 2720
 Total runtime: 534.627 ms
(9 rows)

Re: [GENERAL] pg_upgrade

[Ian Harding]

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
environment variable is set to match.

I'll try that tonight.


On Fri, Feb 15, 2013 at 10:09 AM, Ian Harding  wrote:

> This is interesting, although I'm not sure it's relevant.  From pg_ctl
> source.
>
>
> 00477 
> write_stderr
> (_
> ("\n%s: -w option is not supported when starting a pre-9.1 server\n"),
>
>
> On Fri, Feb 15, 2013 at 9:45 AM, Ian Harding wrote:
>
>>
>> -
>>   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 50432 -c
>> autovacuum=off -c autovacuum_freeze_max_age=20 -c
>> listen_addresses='' -c unix_socket_permissions=0700" start >>
>> "pg_upgrade_server.log" 2>&1
>> waiting for server to
>> start...could
>> not start server
>>
>>
>> The funny thing is, the server starts instantly.
>>
>>
>> On Fri, Feb 15, 2013 at 9:19 AM, Jeff Janes  wrote:
>>
>>> 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_ctl returns failure, even
>>> though
>>> > it starts the server, when run with the -w flag.
>>>
>>> How long does the server take to start?
>>>
>>> If it takes the server more than 1 minute to start, then what you
>>> describe is expected.
>>>
>>> Cheers,
>>>
>>> Jeff
>>>
>>
>>
>

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

[Tom Lane]

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 knows about using the latter
type of condition for indexscans.  Since you need it to convert the
end_at condition into an indexscan to get a fast plan, you lose.

It's conceivable that we could teach the planner about this case, but
I haven't seen enough people doing that to make me think it's worth the
code space and planner cycles.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Merlin Moncure]

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
>> promotion_usages(promotion_id).
>>
>> I have a couple of things I typically want to retrieve, and I'd like those
>> things to be composable.  In this case, finding recently-expired promotions,
>> finding promotions that have a quantity of one, and finding promotions that
>> were used.
>>
>> My approach is to put these conditions into views, then I can join against
>> each one. But that approach is much slower than inlining all the code.
>>
>> How is this typically done?
>
>
> First I am not usually a fan of trying to reduce code duplication by using
> views.   In general, my experience is that this makes it very easy to make
> things slower, and it adds  unexpected optimization hedges in unexpected
> places.
>
> Your problem here seems to be of this sort.  You are joining together two
> views in order to add filters.  These operations are not really guaranteed
> to be the same and so you have an unexpected optimization fence.
>
> My general rule of thumb is to consider moving inline views and WITH clauses
> into views as needed.
>
> Now I think there are a bunch of ways to accomplish what you are trying to
> do here.
>
>  At the risk of jumping straight ahead into advanced functionality and the
> accusations that I am making use of magic wands, I will suggest an
> object-relational approach to reducing code duplication.  This would be to
> eliminate most your filter views and make use instead of table methods.
>
> CREATE FUNCTION recently_expired(promotion) returns bool language sql
> immutable as
> $$
> select $1.ended at > now() - '30 days'::interval;
> $$;
>
> CREATE FUNCTION is_one_time(promotion) returns bool language sql immutable
> as
> $$
> select $1.quantity = 1;
> $$;

Unfortunately from performance point of view that is a much worse way
to do things.  Pushing checks into function like that forces
processing into a iterative model which has a much worse set of
performance gotchas that have essentially no workaround. This is
because there is no way to force the function to be inlined.  What I'd
like to see is to have a new function decoration, INLINE, that
introduces some constraints to how the function can be written and
forces the function to be expanded in the query at plan time.  Only
then will there be a true alternative to using views, especially if
you could inline through a LATERAL function call.

merlin.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David Kerr]

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 according to this it's been running for about an hour.

v3=# select now();
  now
---
 2013-02-15 21:49:34.470466+00
(1 row)

-[ RECORD 5 
]+-
datid| 16389
datname  | v3
pid  | 13905
usesysid | 20415
usename  | workling
application_name |
client_addr  | 10.0.0.132
client_hostname  |
client_port  | 58009
backend_start| 2013-02-15 20:49:09.098768+00
xact_start   | 2013-02-15 20:49:09.189625+00
query_start  | 2013-02-15 20:49:09.198742+00
state_change | 2013-02-15 20:49:09.198743+00
waiting  | f
state| active
query| SELECT count(*) FROM pg_catalog.pg_class AS c WHERE 
c.relname = 'import_jobs' AND c.relpersistence = 'u'

(I actually restarted the application and now there are 4 of these)

Any idea why the query would hang like this?

I don't see it waiting on locks or anything like that.

any ideas?

Thanks


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Case insensitive hstore.

[Ian Lawrence Barwick]

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
>
> 'Type' -> 'original'

As far as I can see from looking at the docs, it's not possible (I
could be wrong though).

> failing that is there a way to lowercase the keys and values of the hstore
> field of the entire table ?

You could create a function like this:


CREATE OR REPLACE FUNCTION hstore_to_lower(val HSTORE)
  RETURNS HSTORE
  LANGUAGE plpgsql
AS $function$
  DECLARE
 hkey TEXT;
  BEGIN
 FOR hkey IN
   SELECT SKEYS(val)
 LOOP

   IF LOWER(hkey) != hkey THEN
 val := val || (LOWER(hkey) || '=>' ||
LOWER((val->hkey::TEXT)))::HSTORE;
 val := val - hkey;
   END IF;

 END LOOP;
 RETURN val;
  END;
$function$

No guarantee of suitability for a particular purpose or of it being
the optimal way of
doing this ;)
Note that any keys converted to lower case will overwrite existing
lower case keys.


HTH

Ian Barwick


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[René Romero Benavides]

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 =
> '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 according to this it's been running for about an hour.
> 
> v3=# select now();
>   now
> ---
>  2013-02-15 21:49:34.470466+00
> (1 row)
> 
> -[ RECORD 5
> ]+-
>  datid| 16389
> datname  | v3
> pid  | 13905
> usesysid | 20415
> usename  | workling
> application_name |
> client_addr  | 10.0.0.132
> client_hostname  |
> client_port  | 58009
> backend_start| 2013-02-15 20:49:09.098768+00
> xact_start   | 2013-02-15 20:49:09.189625+00
> query_start  | 2013-02-15 20:49:09.198742+00
> state_change | 2013-02-15 20:49:09.198743+00
> waiting  | f
> state| active
> query| SELECT count(*) FROM pg_catalog.pg_class AS c WHERE
> c.relname = 'import_jobs' AND c.relpersistence = 'u'
> 
> (I actually restarted the application and now there are 4 of these)
> 
> Any idea why the query would hang like this?
> 
> I don't see it waiting on locks or anything like that.
> 
> any ideas?
> 
> Thanks
-- 
René Romero Benavides @iCodeiExist @PgsqlMx 

Postgresql Tips en español para la comunidad de México e Hispanoamérica.
http://postgresql.org.mx 

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

[mustachebrownbear]

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_table = 'tablename'
 AND trigger_name = 'triggername'
 ) 
THEN
 ;
  
 
END IF ;

END;
$$






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-create-trigger-if-it-does-not-exist-tp1882226p5745434.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Visual query builder for PosgreSQL?

[Satoshi Nagayasu]

(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 ETL tasks, with using JOIN and GROUP BY 
between tables, up to 10 tables on it.

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.

I have already looked a query builder feature in pgAdminIII, but I'm not sure 
whether I can use GROUP BY with it.

Do you have any experience or recommendation about visual query builder for 
PostgreSQL?


You can do joins in the PGAdmin3 query builder but I'm fairly sure you can't do 
group by's.

Apparently, SQL Manager from here:
http://www.sqlmanager.net/en/products/postgresql/manager/
Has a "Visual query builder allowing you to build complicated queries without any 
knowledge of SQL syntax"


Thanks.
I have started trying "SQL Query for PostgreSQL" from EMS, the company 
provides SQL Manager. I think "SQL Query" is the subset of the

SQL Manager, and would be suitable for our purpose.
(and a bit cheaper :)


I may be shot for even suggesting this (and it is a bit of a hassle) but you 
could recreate your table structure in MS Access and use its graphical query 
builder to generate your SQL. It does allow for group by's, counts, etc.


Yeah, I will also look at MS Access as a query builder, although I'm not 
familiar with the software so far.


Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Visual query builder for PosgreSQL?

[Satoshi Nagayasu]

(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 look at http://htsql.org/

It is free open source software intended for "accidental
programmers" -- people who want to pull summarized data from a
database without learning SQL or needing rigorous training.  Its
development was partially funded by grants from foundations,
including the National Science Foundation.  It does support
PostgreSQL and most definitely support counts, sums, etc.  In fact,
it can automagically give you pretty summary graphs with the
ability to drill down to supporting detail.


I second this. I have been trying it out and it is proving quite useful.
The interesting part is that if you use the HTML interface you can get
the SQL sent to the server, helps you learn that also.


Very interesting.

If non-tech people can learn a simple query language for their analytics 
purpose, it would be worth trying.


I think some "abstraction layer" is needed between non-tech users
and DBMS to allow them to issue queries themselves.

I think some query builder could be one of the solutions, and
also some simple query language could be another solution.

I will look into it.

Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade

[Bruce Momjian]

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 might miss unless the PGPORT
> environment variable is set to match.
>  
> I'll try that tonight.

Yes, you are getting close to the answer.  ;-)  The problem is that
Postgres doesn'isn't checking the right port number or socket location
or something else.  This was all improved in Postgres 9.1:

The wait mode is now significantly more robust.  It will not get
confused by non-default postmaster port numbers, non-default
Unix-domain socket locations, permission problems, or stale
postmaster lock files.

I am guessing there is something non-standard about your old cluster,
and 8.4's pg_ctl -w can't handle it.  Tell me what is non-standard and I
can help further.  Another idea is to make the old cluster use defaults
for everything and do the upgrade.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David Kerr]

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 with jruby.
I have standard ruby programs where this does not occur.



- 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 =
- > '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 according to this it's been running for about an hour.
- > 
- > v3=# select now();
- >   now
- > ---
- >  2013-02-15 21:49:34.470466+00
- > (1 row)
- > 
- > -[ RECORD 5
- > ]+-
- >  datid| 16389
- > datname  | v3
- > pid  | 13905
- > usesysid | 20415
- > usename  | workling
- > application_name |
- > client_addr  | 10.0.0.132
- > client_hostname  |
- > client_port  | 58009
- > backend_start| 2013-02-15 20:49:09.098768+00
- > xact_start   | 2013-02-15 20:49:09.189625+00
- > query_start  | 2013-02-15 20:49:09.198742+00
- > state_change | 2013-02-15 20:49:09.198743+00
- > waiting  | f
- > state| active
- > query| SELECT count(*) FROM pg_catalog.pg_class AS c WHERE
- > c.relname = 'import_jobs' AND c.relpersistence = 'u'
- > 
- > (I actually restarted the application and now there are 4 of these)
- > 
- > Any idea why the query would hang like this?
- > 
- > I don't see it waiting on locks or anything like that.
- > 
- > any ideas?
- > 
- > Thanks
- -- 
- René Romero Benavides @iCodeiExist @PgsqlMx 
- 
- Postgresql Tips en español para la comunidad de México e Hispanoamérica.
- http://postgresql.org.mx 
- 
- 
- 
- 
- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general