PostgreSQL: Aug 21, 2008

Thursday, August 21, 2008

Re: [NOVICE] count the number of rows in tables

A B wrote:
> I'd like to fill this table
>
> foo (name varchar(100), rows integer);
>
> with the number of rows for each table that my account has access to.
> But EXECUTE with SELECT ... INTO is not yet implemented.
> So how do I re-write this function?
>
> create or replace function countrows() RETURNS void AS $$
> DECLARE
> rec RECORD;
> nr INTEGER;
> BEGIN
> FOR rec IN SELECT tablename FROM pg_tables WHERE tableowner='myaccount'
> LOOP
> EXECUTE 'SELECT count(*) INTO nr FROM '||rec.tablename;
> EXECUTE 'INSERT INTO foo (name,rows) VALUES ('||rec.tablename||','||nr||')';
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>

insert into foo (name,rows) (select 'employee',count(*) from employee);

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

Re: [PERFORM] Slow query with a lot of data

It looks to me like the work_mem did have an effect.

Your earlier queries had a sort followed by group aggregate at the top, and now its a hash-aggregate. So the query plan DID change. That is likely where the first 10x performance gain came from.

The top of the plan was:

GroupAggregate (cost=11745105.66..12277396.
81 rows=28704 width=12)"
-> Sort (cost=11745105.66..11878034.93 rows=53171707 width=12)"
-> Merge Join (cost=149241.25..1287278.89 rows=53171707 width=12)"
Merge Cond: (b.domain = a.domain)"

and now it is:

"HashAggregate (cost=1685527.69..1686101.77 rows=28704 width=12)"
-> Merge Join (cost=148702.25..1286739.89 rows=53171707 width=12)"
Merge Cond: (b.domain = a.domain)"

The HashAggregate replaced the Sort followed by GroupAggregate at about 1/10 the cost.

It probably only took the first couple hundred MB of work_mem to do this, or less given that you were at the default originally.
Note how the estimated cost on the latter is 1.6 million, and it is 11 million in the first one.

You won't get a large table aggregate significantly faster than this -- you're asking it to scan through 53 million records and aggregate. An explain analyze will be somewhat instructive to help identify if there is more I/O or CPU bound overall as we can compare the estimated cost with the actual times, but this probably won't get very far.

After that, inserting 16M rows requires rather different tuning and bottleneck identification.

On Thu, Aug 21, 2008 at 12:03 AM, Moritz Onken <onken@houseofdesign.de> wrote:

Am 20.08.2008 um 20:06 schrieb Scott Carey:

Ok, so the problem boils down to the sort at the end.

The query up through the merge join on domain is as fast as its going to get. The sort at the end however, should not happen ideally. There are not that many rows returned, and it should hash_aggregate if it thinks there is enough space to do so.

The query planner is going to choose the sort > agg over the hash-agg if it estimates the total number of resulting rows to be large enough so that the hash won't fit in work_mem. However, there seems to be another factor here based on this:

GroupAggregate (cost=11745105.66..12277396.
81 rows=28704 width=12)"
" -> Sort (cost=11745105.66..11878034.93 rows=53171707 width=12)"

" Sort Key: a."user", b.category"
" -> Merge Join (cost=149241.25..1287278.89 rows=53171707 width=12)"

" Merge Cond: (b.domain = a.domain)"

The planner actually thinks there will only be 28704 rows returned of width 12. But it chooses to sort 53 million rows before aggregating. Thats either a bug or there's something else wrong here. That is the wrong way to aggregate those results no matter how much work_mem you have unless I'm completely missing something...

You can try rearranging the query just to see if you can work around this. What happens if you compare the explain on:

select
a."user", b.category, sum(1.0/b.cat_count)::float
from result a, domain_categories b
where a."domain" = b."domain"
and b.depth < 4
and a.results > 100
and a."user" < 30000
group by a."user", b.category

"HashAggregate (cost=1685527.69..1686101.77 rows=28704 width=12)"
" -> Merge Join (cost=148702.25..1286739.89 rows=53171707 width=12)"

" Merge Cond: (b.domain = a.domain)"
" -> Index Scan using domain_categories_domain on domain_categories b (cost=0.00..421716.32 rows=5112568 width=12)"
" Filter: (depth < 4)"
" -> Sort (cost=148415.16..148513.60 rows=39376 width=8)"

" Sort Key: a.domain"
" -> Bitmap Heap Scan on result a (cost=1249.93..145409.79 rows=39376 width=8)"
" Recheck Cond: ("user" < 30000)"
" Filter: (results > 100)"
" -> Bitmap Index Scan on result_user_idx (cost=0.00..1240.08 rows=66881 width=0)"
" Index Cond: ("user" < 30000)"

to

select
c."user", c.category, sum(1.0/c.cat_count)::float
from (select a."user", b.category, b.cat_count
from result a, domain_categories b
where a."domain" = b."domain"
and b.depth < 4
and a.results > 100
and a."user" < 30000 ) c
group by c."user", c.category

"HashAggregate (cost=1685527.69..1686101.77 rows=28704 width=12)"
" -> Merge Join (cost=148702.25..1286739.89 rows=53171707 width=12)"

" Merge Cond: (b.domain = a.domain)"
" -> Index Scan using domain_categories_domain on domain_categories b (cost=0.00..421716.32 rows=5112568 width=12)"
" Filter: (depth < 4)"
" -> Sort (cost=148415.16..148513.60 rows=39376 width=8)"

" Sort Key: a.domain"
" -> Bitmap Heap Scan on result a (cost=1249.93..145409.79 rows=39376 width=8)"
" Recheck Cond: ("user" < 30000)"
" Filter: (results > 100)"
" -> Bitmap Index Scan on result_user_idx (cost=0.00..1240.08 rows=66881 width=0)"
" Index Cond: ("user" < 30000)"

It shouldn't make a difference, but I've seen things like this help before so its worth a try. Make sure work_mem is reasonably sized for this test.

It's exactly the same. work_mem was set to 3000MB.

Another thing that won't be that fast, but may avoid the sort, is to select the subselection above into a temporary table, analyze it, and then do the outer select. Make sure your settings for temporary space (temp_buffers in 8.3) are large enough for the intermediate results (700MB should do it). That won't be that fast, but it will most likely be faster than sorting 50 million + rows. There are lots of problems with this approach but it may be worth the experiment.

I'll try this.

Thanks so far!

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

[SQL] Question on partitioning

Hello , All.

I am not sure if this is the right mailing list to place this question.

If it doesn't, please kindly redirect me to the right list.

I have a giant table with about 10,6 million records.

Queries on it are usually slow, and if I try to do something more elaborate like an INNER JOIN with itself it becomes unnacceptably slow.

I am looking for a way to improve performance.

One of the columns is of type date. Each "day" includes about a few tens of thousands records

And the older a date is the less likely I am to do queries on it.

The objective of the "self join" is to compare data from two different days, looking for diferences.

Ive read that one of the benefits of partitioning is to speed up queries by separating less used records.

My question is if partitioning can be a good way to make the queries faster (specially the self joins) or if it isn't worth trying because it doesn't help on my particular situation.

Please kindly advice me on this

Many thanks in advance for your kind help

Best,

Oliveiros

Re: [pgadmin-hackers] Dialogs Review new patch

On Mon, Aug 18, 2008 at 11:09 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> Hi,
>
> My new patch fixes some issues. Some dialogs seem ready to commit:

Wow - they look excellent. Just some minor thoughts:

- dlgManageMacros - if the size is reduced to minimum, a horizontal
scrollbar is show in the list control, due to the width of the name
column.

- dlgTrigger, dlgSequence and dlgView are quite tall. The latter two
are currently constrained by the height of the privs pane, but perhaps
also by the comments textbox which certainly could be smaller. Not
sure if it would be enough to get them to the standard size though
(perhaps we need two or three standard sizes)?

- The privileges panes doesn't size yet.

- Most dialogues seem to have an unused status bar at the bottom. I
think we should either remove it universally, or make it work
properly. What do you think?

> I don't think I should play with:
> * dlgConnect.xrc
> * dlgSelectConnection.xrc

It may be useful to do them, if only so they size properly with
non-standard fonts, or in other languages.

> The txtValue/chkValue problem is fixed. The wxListCtrl problem is fixed
> too, but with a dirty hack. I don't really like what I did but it works.
> If someone has a better idea, I welcome it.

I've seen a lot worse. I think a comment explaining the purpose of the
function is in order though. I think you should probably commit once
you're at a convenient point, to minimise the risk of bitrot or future
conflicts.

Nice work :-)

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [GENERAL] What's size of your PostgreSQL Database?

Another question, how many people are there maintaining this huge database.
We have about 2T of compressed SAS datasets, and now considering load them into a RDBMS database,
according to your experience, it seems a single PostgreSQL instance can't manage such size databases well, it that right?

--------------------------------------------------
From: "Amber" <guxiaobo1982@hotmail.com>
Sent: Thursday, August 21, 2008 9:51 PM
To: "Mark Roberts" <mailing_lists@pandapocket.com>
Cc: <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] What's size of your PostgreSQL Database?

>
>
>> On Sat, 2008-08-16 at 11:42 +0800, Amber wrote:
>>> Dear all:
>>> We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list:
>>> 1. What's size of your database?
>>> 2. What Operating System are you using?
>>> 3. What level is your RAID array?
>>> 4. How many cores and memory does your server have?
>>> 5. What about your performance of join operations?
>>> 6. What about your performance of load operations?
>>> 7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning.
>>> 8. Single instance or a cluster, what cluster software are you using if you have a cluster?
>>>
>>> Thank you in advance!
>>
>> 1. 2.5-3TB, several others that are of fractional sisize.
>
>
> How many CPU cores and memory does your server have :)
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [PERFORM] Postgres not using array

André Volpato wrote:
> In practice, I have noticed that dual 1.8 is worse than single 3.0. We
> have another server wich
> is a Pentium D 3.0 GHz, that runs faster.
> ...
> Postgres read the array in less than 1 sec, and the other 10s he takes
> 100% of CPU usage,
> wich is, in this case, one of the two cores at 1.8GHz.
>
> I am a bit confused about what CPU is best for Postgres. Our apps is
> mostly read, with
> a few connections and heavy queryes.
> Does it worth a multi-core ?

How are you doing your benchmarking? If you have two or more queries
running at the same time, I would expect the 1.8 Ghz x 2 to be
significant and possibly out-perform the 3.0 Ghz x 1. If you usually
only have one query running at the same time, I expect the 3.0 Ghz x 1
to always win. PostgreSQL isn't good at splitting the load from a single
client across multiple CPU cores.

Cheers,
mark

--
Mark Mielke <mark@mielke.cc>

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

Re: [HACKERS] Questions about HeapTupleHeaderData

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> If I understand correctly then for read path (select) tuple is always
> HeapTuple, because we need support select xmax ... And DatumTuple is
> used for write path (insert/update) and it is "converted" to HeapTuple
> in heap_insert/heap_update function.

No, DatumTuple is used for any free-standing composite-type Datum.
Thinking about it as "write path" and "read path" seems to me to
be missing the mark entirely. In general most tuple-reading code
is supposed to work with either.

regards, tom lane

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

Re: [ADMIN] privilege shedding

>>> dkeeney <dvkeeney@gmail.com> wrote:

> Is there a way to non-reversibly shed privilige within a PostgreSQL
> session?

> I would like the role
> change to persist through the life of the session, without the
option
> of restoring the superuser role.

We could use this in certain circumstances. How about SET
AUTHORIZATION ROLE as syntax? (I was trying to find a reserved word
to use where LOCAL and SESSION are now allowed which would have the
right connotations. I'm entirely happy with this, but it doesn't seem
too bad....)

-Kevin

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

Re: [HACKERS] About a message when "pg_ctl -w start" failed

Tatsuhito Kasahara <kasahara.tatsuhito@oss.ntt.co.jp> writes:
> If a user who perform "pg_ctl -w start" does not have authority to connect
> postgres-database, pg_ctl will return 1 and show "could not start server" message.

pg_ctl is already set up to recognize bad-password errors as meaning the
postmaster is up. You didn't say what auth method you are using, but
I'd think the right fix is to make it recognize other types of
authentication failures as well.

regards, tom lane

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

Re: [ADMIN] pg_log directory

"slamp slamp" <slackamp@gmail.com> writes:
> I am using the PGDG rpms. So it has the default logging configs. its
> rotating daily but i have files there since 2007.

With the RPM's logging config you should only have seven daily log files
in pg_log. Could we see an ls -l of the pg_log directory?

regards, tom lane

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

Re: [PERFORM] PostgreSQL+Hibernate Performance

On Thu, 2008-08-21 at 12:33 +0530, Kranti K K Parisa™ wrote:

> On Wed, Aug 20, 2008 at 8:54 PM, Matthew Wakeling
> <matthew@flymine.org> wrote:
> On Wed, 20 Aug 2008, Kranti K K Parisa™ wrote:
> creating multiple indexes on same column will effect
> performance?
> for example:
>
> index1 : column1, column2, column3
> index2: column1
> index3: column2,
> index4: column3
> index5: column1,column2
>
>
> The sole purpose of indexes is to affect performance.
>
> However, if you have index1, there is no point in having
> index2 or index5.
>
> Matthew
>
> Thanks Matthew,
>
> does that mean i can just have index1, index3, index4?
>

(trying to get the thread back into newest-comments-last order)

Well, yes you can get away with just index1, index3 and index4, and it
may well be the optimal solution for you, but it's not entirely
clear-cut.

It's true that PG can use index1 to satisfy queries of the form "SELECT
x FROM y WHERE column1=somevalue" or "column1=a AND column2=b". It will
not be as fast as an index lookup from a single index, but depending on
the size of the tables/indexes and the selectivity of leading column(s)
in the index, the difference in speed may be trivial.

On the other hand, if you have individual indexes on column1, column2
and column3 but no multi-column index, PG can combine the individual
indexes in memory with a bitmap. This is not as fast as a normal lookup
in the multi-column index would be, but can still be a big win over not
having an index at all.

To make an educated decision you might want to read over some of the
online documentation about indexes, in particular these two sections:

http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html

and

http://www.postgresql.org/docs/8.3/interactive/indexes-bitmap-scans.html

-- Mark

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

Re: [PERFORM] Postgres not using array

André Volpato escreveu:
>
> David Wilson escreveu:
>> On Wed, Aug 20, 2008 at 2:30 PM, André Volpato
>> <andre.volpato@ecomtecnologia.com.br> wrote:
>>
>>
>>> The CPU is 100% used since a few hours ago. Can anyone tell why?
>>>
>>
>> Sounds like you've just got a CPU bound query. The data may even all
>> be in cache.
>>
>> Some information on database size, along with EXPLAIN results for your
>> queries, would help here.
>>
>
> The query itself runs smoothly, almost with no delay.
>

You where right about the cache.
After some experiences, I noticed that the arrays are being used, but
only for a short time...
So, what is slowing down is the CPU (Intel(R) Pentium(R) Dual CPU
E2160 @ 1.80GHz)

In practice, I have noticed that dual 1.8 is worse than single 3.0. We
have another server wich
is a Pentium D 3.0 GHz, that runs faster.

Explain output:
HashAggregate (cost=19826.23..19826.96 rows=73 width=160) (actual
time=11826.754..11826.754 rows=0 loops=1)
-> Subquery Scan b2 (cost=19167.71..19817.21 rows=722 width=160)
(actual time=11826.752..11826.752 rows=0 loops=1)
Filter: (bds_internacoes(200805, 200806, (b2.cod)::text,
'qtdI'::text, 'P'::bpchar) >= 1::numeric)
-> Limit (cost=19167.71..19248.89 rows=2165 width=48) (actual
time=415.157..621.043 rows=28923 loops=1)
-> HashAggregate (cost=19167.71..19248.89 rows=2165
width=48) (actual time=415.155..593.309 rows=28923 loops=1)
-> Bitmap Heap Scan on bds_beneficiario b
(cost=832.53..18031.61 rows=56805 width=48) (actual time=68.259..160.912
rows=56646 loops=1)
Recheck Cond: ((benef_referencia >= 200805)
AND (benef_referencia <= 200806))
-> Bitmap Index Scan on ibds_beneficiario2
(cost=0.00..818.33 rows=56805 width=0) (actual time=63.293..63.293
rows=56646 loops=1)
Index Cond: ((benef_referencia >=
200805) AND (benef_referencia <= 200806))
Total runtime: 11827.374 ms

Postgres read the array in less than 1 sec, and the other 10s he takes
100% of CPU usage,
wich is, in this case, one of the two cores at 1.8GHz.

I am a bit confused about what CPU is best for Postgres. Our apps is
mostly read, with
a few connections and heavy queryes.
Does it worth a multi-core ?

[]´s, ACV

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

[COMMITTERS] pgsql: Autoconf 2.62 will require cache variables to contain "_cv_".

Log Message:
-----------
Autoconf 2.62 will require cache variables to contain "_cv_". Fix our few
noncomplying cases to be future-proof.

Modified Files:
--------------
pgsql:
configure (r1.601 -> r1.602)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/configure?r1=1.601&r2=1.602)
pgsql/config:
c-library.m4 (r1.32 -> r1.33)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/config/c-library.m4?r1=1.32&r2=1.33)

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

Re: [GENERAL] What's size of your PostgreSQL Database?

> On Sat, 2008-08-16 at 11:42 +0800, Amber wrote:
>> Dear all:
>> We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list:
>> 1. What's size of your database?
>> 2. What Operating System are you using?
>> 3. What level is your RAID array?
>> 4. How many cores and memory does your server have?
>> 5. What about your performance of join operations?
>> 6. What about your performance of load operations?
>> 7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning.
>> 8. Single instance or a cluster, what cluster software are you using if you have a cluster?
>>
>> Thank you in advance!
>
> 1. 2.5-3TB, several others that are of fractional sisize.

How many CPU cores and memory does your server have :)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [BUGS] BUG #3818: Cross compilation problems

Richard Evans wrote:
> I'm not sure why the makefiles need the current directory. pwd -W is
> specific to mingw, I think it gives the directory in windows format. It
> has to be changed for cross compilation otherwise you get errors.

What does $(CURDIR) resolve to on mingw? Try the following makefile:

default:
@echo $(shell pwd)
@echo $(shell pwd -W)
@echo $(CURDIR)

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

[NOVICE] count the number of rows in tables

I'd like to fill this table

foo (name varchar(100), rows integer);

with the number of rows for each table that my account has access to.
But EXECUTE with SELECT ... INTO is not yet implemented.
So how do I re-write this function?

create or replace function countrows() RETURNS void AS $$
DECLARE
rec RECORD;
nr INTEGER;
BEGIN
FOR rec IN SELECT tablename FROM pg_tables WHERE tableowner='myaccount'
LOOP
EXECUTE 'SELECT count(*) INTO nr FROM '||rec.tablename;
EXECUTE 'INSERT INTO foo (name,rows) VALUES ('||rec.tablename||','||nr||')';
END LOOP;
END;
$$ LANGUAGE plpgsql;

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

Re: [ADMIN] pg_log directory

On Wed, Aug 20, 2008 at 9:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> That's just the server message log. While you could blow away the files
> without any ill effect, soon enough you'd have another 40GB. You need
> to either dial down the verbosity of your what-to-log settings, or
> modify the where-to-log settings to recycle log files more frequently,
> or both. In particular it sounds like you have set log_statement = all
> without, perhaps, understanding the consequences. Read the fine manual
> here:
> http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html
>
> regards, tom lane
>

I am using the PGDG rpms. So it has the default logging configs. its
rotating daily but i have files there since 2007. is there a config
option to delete these files? i looked at the manual and i guess i
must be missing the option i want.

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

[JDBC] jdbc connectivity with postgresql

am using

jdk1.5.0_14
jdbc3 -- postgresql-8.2-508.jdbc3
psql 8.2.4

its really much confusing in connectivity..please help me giving answers to this question

1. where to save postgresql-8.2-508.jdbc3
(all have giving different ideas)

2.how to set the class path?is i have to download tomcate....

3.can you please give me a clear picture to set class
path with postgresql-8.2-508.jdbc3

4. how to run my java application files

RE: [pgsql-es-ayuda] Select en una BD e insert en otra BD

Hola Laura...

On Thu, 21 Aug 2008 14:11:06 +0200, Laura reiva wrote
> Hola, gracias por tu respuesta. Pero el principal problema es hacer
> ésto sobre dos bases de datos distintas, que no he encontrado forma
> de hacerlo sobre dos bases de datos con insert into... select.

Te fijaste en el contrib dblink?. Seguramente por ahí vas a encontrar la solución.

> Gracias.

Por nada.

pd1: Por favor, no hagas top-posting
pd2: Por favor, evita el (innecesario) html en tus mails a la lista. ;)

-
-------------------------------------------
Sebastián Villalba
A. Gestión Informática
Facultad de Cs. Médicas
U.N.C. - Argentina
-------------------------------------------

--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [HACKERS] A smaller default postgresql.conf

David Fetter wrote:

> And we're back to man pages and CHM files.

So, did anyone else try to generate man pages? I did "make man" and ran
into several issues.

The first is that D2MDIR needs to be specified manually. I assume this
is how everyone does it, so I did that.

The second is that the Perl script as packaged by Debian doesn't work --
it is missing a handful of HTML entities, and it errors out before
generating anything. I had to patch it thusly:

The third problem I ran into is that manpages that are supposed to go
into the "l" section do not; the filename ends in a dot:

$ ls | tail -10
SPI_saveplan.
SPI_scroll_cursor_fetch.
SPI_scroll_cursor_move.
START TRANSACTION.
TRUNCATE.
UNLISTEN.
UPDATE.
VACUUM.
VALUES.
version.sgml

The makefile is passing "--section l" so I'm not sure what's going wrong
here.

And lastly, I couldn't figure out what decides which manpages are
generated, to make it pick the text we want to publish.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

[pgsql-es-ayuda] Error en disparador

Qué tal tengo varias tablas y en ellas trato de hacer funcionar una
vista.
La vista actua de diferente manera cuando es inserción o actualización.
Con la inserción no hay problema alguno pero con la actualización no la
realiza de ninguna manera, no comprendo bien si el error es lógico o no
pero pido su consejo para que puedan apoyarme.
Aquí el disparador:

CREATE OR REPLACE FUNCTION trigger_departamento() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (New.id_departamento IN(SELECT id_departamento FROM
departamento_error)) THEN
DELETE FROM departamento_error WHERE
id_departamento=New.id_departamento;
END IF;

IF (
(New.id_departamento IN(SELECT id_departamento FROM
departamento_excepciones))
OR
(New.id_departamento IN(SELECT id_departamento FROM
departamento))
OR
(New.id_departamento IN(SELECT id_departamento FROM
departamento_agregados))
)
THEN
RETURN NULL;
END IF;
ELSIF (TG_OP = 'UPDATE') THEN
IF (New.id_departamento IN(SELECT id_departamento FROM
departamento_agregados)) THEN
UPDATE departamentos_agregados SET
nombre_departamento=New.nombre_departamento WHERE
id_departamento=New.nombre_departamento;
RETURN NULL;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER trigger_departamento BEFORE INSERT OR UPDATE ON
departamento FOR EACH ROW EXECUTE PROCEDURE trigger_departamento();

Gracias de antemano por la ayuda.

--
Miguel Ángel Villagómez Díaz
Sayab Technologies S.C.
Tel. 3616-9100

--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

[HACKERS] About a message when "pg_ctl -w start" failed

Hi.

When we perform "pg_ctl -w start", postgres try to connect to
postgres-database until timeout.

If a user who perform "pg_ctl -w start" does not have authority to connect
postgres-database, pg_ctl will return 1 and show "could not start server" message.
But, in fact server is already starting. Therefore, users sometimes confuse.

I think that if we failed on test_postmaster_connection(), pg_ctl might
want to return 0 and show the message like following...
"server starting, but could not connect server. Check your authority."

Your thoughts?

Best regards.
--
Tatsuhito Kasahara
kasahara.tatsuhito@oss.ntt.co.jp

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

[pgadmin-support] problem with jdbc with postgresql

am using

jdk1.5.0_14
jdbc3 -- postgresql-8.2-508.jdbc3
psql 8.2.4

its really much confusing in connectivity..please help me giving answers to this question

1. where to save postgresql-8.2-508.jdbc3
(all have giving different ideas)

2.how to set the class path?is i have to download tomcate....

3.can you please give me a clear picture to set class
path with postgresql-8.2-508.jdbc3

4. how to run my java application files

[BUGS] BUG #4368: problem with jdbc postgresql

The following bug has been logged online:

Bug reference: 4368
Logged by: devi
Email address: lakshmidevi83@gmail.com
PostgreSQL version: psql 8.2.4
Operating system: windows xp
Description: problem with jdbc postgresql
Details:

am using

jdk1.5.0_14
jdbc3 -- postgresql-8.2-508.jdbc3
psql 8.2.4

its really much confusing in connectivity..please help me giving answers to
this question

1. where to save postgresql-8.2-508.jdbc3
(all have giving different ideas)

2.how to set the class path?is i have to download tomcate....

3.can you please give me a clear picture to set class
path with postgresql-8.2-508.jdbc3

4. how to run my java application files

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

Re: [GENERAL] Single character bitfields

In response to "Andrew Maclean" <andrew.amaclean@gmail.com>:

> I have a large database and I want to have several fields (among many)
> that are single character fields (in fact they are bitfields).
>
> 1) Is char(1) the most efficient way to store these fields? If not
> what is better?
> 2) I need to test the field against constants, e.g if the field is
> called source then I need tests like:
> IF source = 0x10 THEN ...
> This does not work in plpgsql functions (source is character(1)),
> so what is the solution?

I'm not sure I understand what you're trying to accomplish, but there's
a bit string type:
http://www.postgresql.org/docs/8.3/static/datatype-bit.html

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

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

[ADMIN] privilege shedding

Is there a way to non-reversibly shed privilige within a PostgreSQL
session?

I would like to start a session as a superuser role, set up some views
and triggers as superuser, and then change role to a lesser role for
the remainder of the session.

It seems that if you use 'set role' for this, you get the lesser role,
but the original (superuser) role can be restored by another 'set
role' statement, without any re-authentication. I would like the role
change to persist through the life of the session, without the option
of restoring the superuser role.

Thank you,
David

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

Re: [HACKERS] migrate data 6.5.3 -> 8.3.1

Tom Lane wrote:
> Of course, since you got the data migrated you might not care anymore.

That's what i've done:
1. pg_dump database from 6.5.3
2. iconv from windows-1251 charset to utf-8.
3. cutted all AGGREGATEs
4. succesefully imported all data to 8.3.1 without errors.

Seems to me everything now working as it should. Thanks everyone :)

--
alexander lunyov

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

Re: [GENERAL] function SETOF return type with variable columns?

On Wed, Aug 20, 2008 at 12:59 PM, James Neff
<james.neff@tethyshealth.com> wrote:
> Greetings,
>
> Is it possible to have a function with a return type of SETOF that has
> variable number of return columns?

On Wed, Aug 20, 2008 at 10:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
>> PostgreSQL functions are for the most part strictly bound to their
>> return type.
>
> There is, however, the trick of declaring the function as "returns
> record" and then specifying the names and types of the output columns
> in the calling query. I'm not sure how practical that is to use with
> a plpgsql function, and in any case it's not the syntax the OP asked
> for; but it seems worth mentioning in this thread.

Here's another approach, using a refcursor: This is cheating
according to the rules set by the OP, but it's a great way to provide
a flexible way to return data from the database via a single function.

create or replace function doit() returs refcursor as
$$
declare
r refcursor value 'result';
begin
/* some query that puts data in refcursor */
end;
$$ language plpgsql;

-- from psql/app
begin;
select doit();
fetch all from result;
commit;

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

Re: [BUGS] Postgresql v8.3.3 + Perl v5.10

Bug in that, that I install PG on server with perl 5.10 installed, but PG
fails to run any PLPERL code
How to fix?

----- Original Message -----
From: "Peter Eisentraut" <peter_e@gmx.net>
To: <pgsql-bugs@postgresql.org>
Cc: <Eugen.Konkov@aldec.com>
Sent: Thursday, August 21, 2008 2:55 PM
Subject: Re: [BUGS] Postgresql v8.3.3 + Perl v5.10

> Eugen.Konkov@aldec.com wrote:
>> Is this possible?
>
> That is a question, not a bug.
>
> But yes, it is possible.

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

Re: [GENERAL] plpgsql - sorting result set

On Thu, Aug 21, 2008 at 1:03 AM, Bob Gobeille <bob.gobeille@hp.com> wrote:
> On Aug 20, 2008, at 10:11 PM, Gobeille, Robert wrote:
>> On Aug 20, 2008, at 7:37 PM, Merlin Moncure wrote:
>>> On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille
>>>> Is it possible to sort a result set in plpgsql?
>>>>
>>>> That is, after building up the result set with RETURN NEXT from
>>>> multiple
>>>> queries, I'd like to sort the set before returning.
>>>>
>>>> I'm still using 8.1 if that is an issue.
>>>
>>> Have you already ruled out:
>>>
>>> select * from (select * from your_func()) order by something?
>>>
>>> if so, why?
>>> merlin
>>
>> I've ruled this out because I do multiple queries. Here is my
>> function. I want to reorder the result set (output table) before
>> returning.
>>
>> CREATE or REPLACE function uploadtree2path(uploadtree_pk_in int)
>> returns setof uploadtree as $$
>> DECLARE
>> UTrec uploadtree;
>> UTpk integer;
>> sql varchar;
>> BEGIN
>>
>> UTpk := uploadtree_pk_in;
>>
>> WHILE UTpk > 0 LOOP
>> sql := 'select * from uploadtree where uploadtree_pk=' || UTpk;
>> execute sql into UTrec;
>>
>> IF ((UTrec.ufile_mode & (1<<28)) = 0) THEN RETURN NEXT UTrec;
>> END IF;
>> UTpk := UTrec.parent;
>> END LOOP;
>> RETURN;
>> END;
>> $$
>> LANGUAGE plpgsql;
>
> Merlin,
> I just reread what you wrote. ;-) Yes, your select * from (select * from
> your_func) would work. The function caller itself can sort the results
> (outside of postgres). I could also have a second function call the above,
> sorting the results. These just seem kludgy. That's why I was wondering
> if it were possible to select * from (select * from function_return_set)
> order by. But I see no way to reference the table to be returned.

You can always pass the order by clause (or hardcode it) into the
execute statement. Also if you are using 8.3 you may want to check
out to the new improvements to 'execute'...using.

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: [HACKERS] A smaller default postgresql.conf

David Fetter wrote:
> And we're back to man pages and CHM files.
>
> How big a project would that latter be?

CHM files already exist. (At least I think that CHM == HTML Help == Windows
help system.)

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

Re: [GENERAL] [ADMIN] Regarding access to a user

Shashwat_Nigam wrote:
> Thanks for the help. But still the user is able to see all
> the databases. I defined something like this
>
> # IPv4 local connections:
> host all all 127.0.0.1/32 md5
> host HMRI_database hmri 127.0.0.1/32 md5
>
>
> in the above case hmri is user and HMRI_database. But still
> when user is log in as 'hmri' it is able to access all the databases.
> Please provide a solution for the same.

Remove the first line, and you'll be fine.

PostgreSQL uses the first line in pg_hba.conf that matches the
incoming connection.

Yours,
Laurenz Albe

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

Re: [BUGS] Postgresql v8.3.3 + Perl v5.10

Eugen.Konkov@aldec.com wrote:
> Is this possible?

That is a question, not a bug.

But yes, it is possible.

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

Re: [BUGS] BUG #4365: Need 24x 7 monitoring software for Postgresql

In response to "Gregg" <GreggBeall@livenation.com>:
>
> The following bug has been logged online:
>
> Bug reference: 4365
> Logged by: Gregg
> Email address: GreggBeall@livenation.com
> PostgreSQL version: 8.3
> Operating system: Sus 10
> Description: Need 24x 7 monitoring software for Postgresql
> Details:
>
> I need 24 x7 monitoring software for Database to give stats and status on
> the databases

Not sure how this would qualify as a bug ... I'm assuming this was
simply the first communication mechanism you found.

You'll be better off joining the pgsql-general@ mailing list and asking
there:
http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-long-full&extra=pgsql-general

Personally, I use Nagios for 24x7 monitoring. Nagios has a number of
useful pgsql plugins. For stats gathering, I use mrtg, which I like
because it's very flexible.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

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

Re: [HACKERS] Questions about HeapTupleHeaderData

Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> I try to understand why HeapTupleHeaderData structure has t_datum
>> member. This is use only on few places and from my point of view this
>> information should be stored in the HeapTupleData structure or split
>> HeapTupleHeaderData it into two structures (DatumTupleHeaderData).
>
> Then (a) we'd have two struct definitions that have to stay in sync
> and (b) we'd have to cast between HeapTupleHeader and DatumTupleHeader
> pointer types in a bunch of places, adding notational messiness with
> no advantage.

If I understand correctly then for read path (select) tuple is always
HeapTuple, because we need support select xmax ... And DatumTuple is
used for write path (insert/update) and it is "converted" to HeapTuple
in heap_insert/heap_update function.

Hmm I'm looking into executor and executor "converts" it when tuple is
copied from buffer to work memory and processed.

However, Other kind of question is about space optimization.

From composite data type point of view it seems to me that we waste a
lot of space. For example varlena is always 4 bytes and from infomask we
need only HASVARWIDTH, HEAP_HASEXTERNAL and HASNULLS which could be
placed into infomask2 and all transaction information should be in
infomask. By my opinion we can save minimal 8 bytes there per composite
type with reordering and of course minimal tuple could be smaller too.
OK any this changes bump page layout version which is not my goal now,
but it could be idea for future development.

Zdenek

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

Re: [GENERAL] Interval Formatting -> Convert to timestamp

On Thu, 2008-08-21 at 11:53 +0200, Tomasz Ostrowski wrote:
> On 2008-08-21 11:09, Ow Mun Heng wrote:
>
> > I want to find out if there's a method to change this
> > select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS')
> > to something like
> > 24+9(hours) = 33:18:42 instead of returning It as 09:19:42
>
> That's because 1 day doesn't always have 24 hours, because there are
> daylight saving times. 1 month can also have various number of days or
> hours.
>
> If you want 1 day to be always 24 hours then:
> select date_part('day', t)*'24h'::interval + t-date_trunc('day',t)
> from (select '1 day 09:18:42.37996'::interval as t) as s;
>
> It will not work for intervals with months. I'm not sure for negative ones.
>

Thanks for all the suggestions, but in the end, I went back to
old-school solution, meaning, choose the lowest denominator which is
epoch and seconds.

extract('epoch' from (max(a.delta))/3600)

where a.delta = '1 day 09:18:42.37996'

which returns me something in the form of X.YZ (14.11) or sotmehing like
that..

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

[pgadmin-hackers] SVN Commit by dpage: r7413 - trunk/pgadmin3/pgadmin/gqb

Author: dpage

Date: 2008-08-21 11:03:20 +0100 (Thu, 21 Aug 2008)

New Revision: 7413

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7413&view=rev

Log:
Formatting fix.

Modified:
trunk/pgadmin3/pgadmin/gqb/gqbView.cpp

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

[pgadmin-hackers] SVN Commit by dpage: r7412 - trunk/pgadmin3/pgadmin/gqb

Author: dpage

Date: 2008-08-21 11:02:58 +0100 (Thu, 21 Aug 2008)

New Revision: 7412

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7412&view=rev

Log:
Don't allow the relation and column cell names to be incorrectly edited.

Modified:
trunk/pgadmin3/pgadmin/gqb/gqbGridProjTable.cpp

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

Re: [pgsql-www] request for sponsors page

On Wed, 20 Aug 2008, Josh Berkus wrote:

> Dave,
>
>> My thoughts exactly. Weren't you drafting a policy for sponsor
>> recognition at one point?
>
> Yes, but:
>
> a) we don't have technology to track sponsors right now (working on it),

there are not so many sponsors we can't track. I think, like in our case,
if developer(s) explicitly acknowledge a company for support in
publicly available document, presented on PostgreSQL conference, it
should be a good recomendation.

We acknowledged jfg://networks several times
in our presentation ob PGCon and there are should be no doubts that
it is jfg://networks, which supported GIN development and continue support
of our current work on GIN improvement.

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [GENERAL] Interval Formatting -> Convert to timestamp

On 2008-08-21 11:09, Ow Mun Heng wrote:

> I want to find out if there's a method to change this
> select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS')
> to something like
> 24+9(hours) = 33:18:42 instead of returning It as 09:19:42

That's because 1 day doesn't always have 24 hours, because there are
daylight saving times. 1 month can also have various number of days or
hours.

If you want 1 day to be always 24 hours then:
select date_part('day', t)*'24h'::interval + t-date_trunc('day',t)
from (select '1 day 09:18:42.37996'::interval as t) as s;

It will not work for intervals with months. I'm not sure for negative ones.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

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

Re: [pgsql-www] request for sponsors page

On Wed, 20 Aug 2008, Josh Berkus wrote:

> On Wednesday 20 August 2008 13:22, Oleg Bartunov wrote:
>> Hi there,
>>
>> I and Teodor ask about adding our sponsor jfg://networks
>> (http://www.over-blog.com/) to the sponsor's page
>> http://www.postgresql.org/about/sponsors.
>
> Right now, we only put up year-round sponsors of full-time developers.
> Someday we'll be able to list other sponsors, but not at the moment.

Hmm, I think the more important factor here is the net result of support
and it'd be fair to list a list of features supported. There are many
important contributions developed by non full-time developers.

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

Re: [ADMIN] Regarding access to a user

From: Shashwat_Nigam@satyam.com
To: aroravishal22@hotmail.com; pgsql-admin@postgresql.org
CC: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-admin@postgresql.org
Date: Thu, 21 Aug 2008 14:55:05 +0530
Subject: Re: [ADMIN] Regarding access to a user

Hi Vishal

Ya you are right and the order is same and the command is like that only.

I am opening it through pgAdmin only..

If you are using pgAdmin, you have to keep maintainence db as postgres for which you do not have the entry for that in pg_hba file. I would suggest you to use psql -U <username> command line option to connect.

Thanks & Regards,

Shashwat Nigam

| Software Engineer | Geographical Information System

Satyam Computer Services Limited, Hyderabad | MOBILE: +919951467324 |

From: Vishal Arora [mailto:aroravishal22@hotmail.com]
Sent: Thursday, August 21, 2008 2:47 PM
To: Shashwat_Nigam; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Regarding access to a user

Hi Shashwat,

I presume your user is hmri and the database is hmri_database, is it correct?

if yes - your pg_hba.conf entry should be as following (if you are connecting to postgres on the same machine)

host hmri_database hmri 127.0.0.1/32 md5

Can you tell me what command are you using to connect to the server and what environment is it?

Regards

Vishal

From: Shashwat_Nigam@satyam.com
To: aroravishal22@hotmail.com; pgsql-admin@postgresql.org; Kevin.Grittner@wicourts.gov
CC: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-admin@postgresql.org
Date: Thu, 21 Aug 2008 14:35:43 +0530
Subject: Re: [ADMIN] Regarding access to a user

Dear Vishal

Thanks for the help but by doing this an error is generated at the time when the user is trying to log in with the following message:

Access to database denied

The server doesn't grant access to the database: the server reports

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "hmri", database "postgres", SSL off

To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like

host all all 192.168.0.0/24 md5

This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.

You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

Please provide futher proceedings

Thanks & Regards,

Shashwat Nigam

| Software Engineer | Geographical Information System

Satyam Computer Services Limited, Hyderabad | MOBILE: +919951467324 |

From: Vishal Arora [mailto:aroravishal22@hotmail.com]
Sent: Thursday, August 21, 2008 2:29 PM
To: Shashwat_Nigam
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Regarding access to a user

> From: Shashwat_Nigam@satyam.com
> To: Kevin.Grittner@wicourts.gov
> CC: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-admin@postgresql.org
> Date: Thu, 21 Aug 2008 14:23:51 +0530
> Subject: Re: [ADMIN] Regarding access to a user
>
> Dear Kevin
> Thanks for the help. But still the user is able to see all the databases. I defined something like this
>
> # IPv4 local connections:
> host all all 127.0.0.1/32 md5
> host HMRI_database hmri 127.0.0.1/32 md5

You have to comment out the first entry made in your hba file. In this case it checks for the first line where all users are allowed to access all databases so it does not checks the second line.

>
> in the above case hmri is user and HMRI_database. But still when user is log in as 'hmri' it is able to access all the databases.
> Please provide a solution for the same.
>
> Thanks & Regards,
> Shashwat Nigam
> | Software Engineer | Geographical Information System
> Satyam Computer Services Limited, Hyderabad | MOBILE: +919951467324 |
>
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Wednesday, August 20, 2008 6:41 PM
> To: pgsql-admin@postgresql.org; Shashwat_Nigam
> Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org
> Subject: Re: [ADMIN] Regarding access to a user
>
> >>> Shashwat_Nigam <Shashwat_Nigam@satyam.com> wrote:
>
> > Now what I want is to set privilege that whenever the user log in as
> Us
> > he can only see database Y, none other than that. The user Us could
> have all
> > rights for database Y but can't go for any other database (X or Z).
>
> The first thing you need to do is configure "host based
> authentication":
>
> http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html
>
> Among other things, this allows you to restrict which users can
> connect to which databases.
>
> -Kevin
>
>
> DISCLAIMER:
> This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Search for videos of Bollywood, Hollywood, Mollywood and every other wood, only on Live.com Try it now!

DISCLAIMER:
This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.

Searching for weekend getaways? Try Live.com Try it!

Do you have a You@live.in id yet? Get it now. Sign up for a Windows Live ID Try it!

[BUGS] Postgresql v8.3.3 + Perl v5.10

Is this possible?

_____

Eugen

Re: [GENERAL] Interval Formatting -> Convert to timestamp

On 21/08/2008 10:09, Ow Mun Heng wrote:

> I want to find out if there's a method to change this
>
> select to_char('1 day 09:18:42.37996'::interval,'HH24:MI:SS')
>
> to something like
>
> 24+9(hours) = 33:18:42 instead of returning It as 09:19:42

I had to do something like this recently when adding up the durations of
music CD tracks (stored as INTERVALs), and I wanted the totals returned
in terms of minutes rather than hours:

create or replace function format_duration(theDuration interval)
returns text
as
$$
select
to_char(extract(hour from $1) * 60 + extract(minute from $1), 'FM9,999')
|| to_char(extract(second from $1), '":"FM00');
$$
language sql;

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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

Re: [JDBC] Tracing SQL statements per page/request

Shahaf Abileah <shahaf 'at' redfin.com> writes:

> If not, can someone recommend another approach? In case it helps, we use
> hibernate as our O-R mapping so hibernate is responsible for generating most
> (not all) of our SQL queries (some are still issued by direct SQL). Also, we

in pg is a good idea, as already suggested, but if many other
unrelated clients also access pg and thus it's difficult to
isolate the queries you're interested in, why not
hibernate.show_sql + appropriate logging on your direct SQL
layer?

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36

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

Re: [ADMIN] Regarding access to a user

Hi Vishal

Ya you are right and the order is same and the command is like that only.

I am opening it through pgAdmin only..

Thanks & Regards,

Shashwat Nigam

| Software Engineer | Geographical Information System

Satyam Computer Services Limited, Hyderabad | MOBILE: +919951467324 |

Dear Vishal

Thanks for the help but by doing this an error is generated at the time when the user is trying to log in with the following message:

“

Access to database denied

The server doesn't grant access to the database: the server reports

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "hmri", database "postgres", SSL off

The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like

host all all 192.168.0.0/24 md5

This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.

“

Please provide futher proceedings

Thanks & Regards,

Shashwat Nigam

| Software Engineer | Geographical Information System

Satyam Computer Services Limited, Hyderabad | MOBILE: +919951467324 |

Search for videos of Bollywood, Hollywood, Mollywood and every other wood, only on Live.com Try it now!

Searching for weekend getaways? Try Live.com Try it!

Thursday, August 21, 2008

Blog Archive