Re: [GENERAL] find a string contained in an attribute

[Pavel Stehule]

2010/3/24 Szymon Guz :
> 2010/3/24 Karina Guardado 
>>
>> Hi,
>>
>> I want to know if it is possible to search for a string of characters
>> inside an attribute for example I have the following table and values
>>
>> cod_unidad | nombre_uni
>> +-
>>   1 | Facultad de Ciencias Naturales y Matemática
>>   2 | Facultad de Ciencias Humanidades
>>   3 | Facultad de Ingeniería
>>   4 | Facultad de Agronomía
>>   5 | Oficinas Centrales
>>   6 | test
>>
>>
>> I want to retrieve all the rows where I find the word Ciencias for
>> example. Is there a function or with select is possible to do it?
>>
>> thanks in advance,
>>
>> karina
>> El Salvador, Centroamerica
>
> For example something like this should work:
> SELECT * FROM table WHERE nombre_uni like '%Ciencias%';
> more you can find
> here: http://www.postgresql.org/docs/8.4/interactive/functions-matching.html
> regards
> Szymon Guz

better to use a fulltext it is much faster on tables larger than small

create index foo on tab using gin(to_tsvector('simple', nombre_uni))

select * from tab where to_tsvector('simple', nombre_uni) @@
to_tsquery('simple', 'Ciencias');

Regards
Pavel Stehule

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

[GENERAL] Side effect of synchronous_commit = off

[Yan Cheng CHEOK]

I was wondering whether setting synchronous_commit = off will have the 
following side effect.

(1) Process A issues UPDATE command on row x, from false to true.
(2) After that, Process B READ from row x.

Is it possible that when Process B start to read row x, the "true" value is not 
being "flushed" to the table. Hence, process B will read the row x as false?

If this situation will happen, is it possible that Process B may issues a 
command, use to "flush all" pending data to be written to disk?

Thanks and Regards
Yan Cheng CHEOK


  


-- 
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] Revoking CREATE TABLE

[Adrian von Bidder]

On Wednesday 24 March 2010 17.28:37 Tony Webb wrote:
> #revoke create on schema public from public;
> REVOKE

Note that this will *only* revoke the create right from "PUBLIC" and not 
from any individual role that may have the right.  PostgreSQL unfortunately 
does not have a "revoke ... from ALL".

So you need to revoke from all individual roles that have the right on this 
database/schema.

cheers
-- vbi

-- 
Vorteil kleiner Menschen:  Sie werden bei Regen später naß.


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] Side effect of synchronous_commit = off

[Guillaume Lelarge]

Le 25/03/2010 08:49, Yan Cheng CHEOK a écrit :
> I was wondering whether setting synchronous_commit = off will have the 
> following side effect.
> 
> (1) Process A issues UPDATE command on row x, from false to true.
> (2) After that, Process B READ from row x.
> 
> Is it possible that when Process B start to read row x, the "true" value is 
> not being "flushed" to the table. Hence, process B will read the row x as 
> false?
> 

No, it's not possible.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

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

[GENERAL] Get the list of permissions/privileges on schema

[dipti shah]

Hi,

Could any one please tell me how to get list of all the permissions on the
schema (or any postgresql objects), stored them somewhere before executing
stored procedure and then restore them?

Thanks,
Dipti

Re: [GENERAL] Get the list of permissions/privileges on schema

[Ashesh Vashi]

You should look into the pg_class table : relacl attribute for the
permissions on any object.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company

On Thu, Mar 25, 2010 at 2:37 PM, dipti shah  wrote:

> Hi,
>
> Could any one please tell me how to get list of all the permissions on the
> schema (or any postgresql objects), stored them somewhere before executing
> stored procedure and then restore them?
>
> Thanks,
> Dipti
>

Re: [GENERAL] Get the list of permissions/privileges on schema

[Sergey Konoplev]

On 25 March 2010 12:14, Ashesh Vashi  wrote:
> You should look into the pg_class table : relacl attribute for the
> permissions on any object.

Is it correct to set it directly?

> On Thu, Mar 25, 2010 at 2:37 PM, dipti shah  wrote:
>> Could any one please tell me how to get list of all the permissions on the
>> schema (or any postgresql objects), stored them somewhere before executing
>> stored procedure and then restore them?

May be you are looking for something like SECURITY DEFINER option?
http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html

-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802

-- 
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] Get the list of permissions/privileges on schema

[Ashesh Vashi]

On Thu, Mar 25, 2010 at 2:54 PM, Sergey Konoplev  wrote:

> On 25 March 2010 12:14, Ashesh Vashi 
> wrote:
> > You should look into the pg_class table : relacl attribute for the
> > permissions on any object.
>
> Is it correct to set it directly?
>

Of course not...
This is just way to fetch the list of permission on any object.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company

Re: [GENERAL] Get the list of permissions/privileges on schema

[dipti shah]

Thanks a lot guys but I am not looking for security definer function. I know
it. My requirements are very complicated and I have to nailed down the
stuffs by storing schema permissions somewhere, execute my store procedure,
and restored the stored schema permissions. Like this I would make sure that
thogh my store procedure manipulates schema permissions, at the end, users
will have their permissions intact.

Thanks,
Dipti

On Thu, Mar 25, 2010 at 2:59 PM, Ashesh Vashi  wrote:

>
>
>  On Thu, Mar 25, 2010 at 2:54 PM, Sergey Konoplev wrote:
>
>> On 25 March 2010 12:14, Ashesh Vashi 
>> wrote:
>> > You should look into the pg_class table : relacl attribute for the
>> > permissions on any object.
>>
>> Is it correct to set it directly?
>>
>
> Of course not...
> This is just way to fetch the list of permission on any object.
>
>  --
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise Postgres Company
>

[GENERAL] What datatype should I use to store an IP Address?

[Andre Lopes]

Hi,

I'm using Varchar(16) to store an IP Address. PostgreSQL offers another
datatypes to store IP's or it is OK to store in Varchar(16)?

Best Regards,

Re: [GENERAL] What datatype should I use to store an IP Address?

[Michał Pawlikowski]

>On Thu, Mar 25, 2010 at 11:25 AM, Andre Lopes  wrote:
> Hi,
>
> I'm using Varchar(16) to store an IP Address. PostgreSQL offers another
> datatypes to store IP's or it is OK to store in Varchar(16)?
>
> Best Regards,
>

Yes, just look here:
http://www.postgresql.org/docs/8.4/static/datatype-net-types.html

-- 
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] What datatype should I use to store an IP Address?

[Devrim GÜNDÜZ]

On Thu, 2010-03-25 at 11:28 +0100, Michał Pawlikowski wrote:
> > I'm using Varchar(16) to store an IP Address. PostgreSQL offers
> > another datatypes to store IP's or it is OK to store in Varchar(16)?
> >
> Yes, just look here:
> http://www.postgresql.org/docs/8.4/static/datatype-net-types.html

Alternatively, you can use ip4r: 

 http://pgfoundry.org/projects/ip4r

"ip4 and ip4r are types that contain a single IPv4 address and a range
of IPv4 addresses respectively. They can be used as a more flexible,
indexable version of the cidr type."

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

[GENERAL] Achieving ordered update

[Allan Kamau]

Hi,
A classic problem. I would like to assign integer values (from a
sequence) to records in a table based on the order (of contents) of
other field(s) in the same table.
I come across the solution before but I simply may not recall and a
search returns no clues. Please point me to a resource with this
solution.

Allan.

-- 
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] Achieving ordered update

[Thom Brown]

On 25 March 2010 12:02, Allan Kamau  wrote:

> Hi,
> A classic problem. I would like to assign integer values (from a
> sequence) to records in a table based on the order (of contents) of
> other field(s) in the same table.
> I come across the solution before but I simply may not recall and a
> search returns no clues. Please point me to a resource with this
> solution.
>
> Allan.
>
> -
>
Rank and a window function?

http://www.postgresql.org/docs/8.4/static/functions-window.html
http://www.postgresql.org/docs/8.4/static/tutorial-window.html

Thom

[GENERAL] pgfoundry registration

[Krzysztof Nienartowicz]

Hello,
Is registration to pgFoundry closed on purpose? I get confirmation emails but 
am greeted with:


Access denied

Credentials you entered do not correspond to valid account.

Could you help or advise, please?

Best regards,
Krzysztof

[GENERAL] How many Mandatory Process are there in Postgres

[Tadipathri Raghu]

Hi All,

When we start the postgres server, the writer process, wal process,
postmaster, autovacuum ( if autovacuum is on), stats collector will come
into picture as mandotory process. My question is, is there any processes
apart from these process, what are the mandotory process come along with the
postgres server and how many sleeping processes are there.

Few important question about Postgres Architecture
==
1. When does temp_buffer comes into existence in a database session ( like
when shared_buffer completely filled or any wal_buffer filled) ?
2. What is process array in shared memory?
3. maintenance_work_mem is used for vacuuming(does this memory allocated if
autovacuum is off)?
4. As oracle,  Postgres doesnt have any undo_tablespace, for rollback
transaction. But it is handled in BEGIN /END block. So question is where the
transaction data is stored.

Thanks in Advance

Regards
Raghavendra

Re: [GENERAL] How many Mandatory Process are there in Postgres

[tv]

> Hi All,
>
> When we start the postgres server, the writer process, wal process,
> postmaster, autovacuum ( if autovacuum is on), stats collector will come
> into picture as mandotory process. My question is, is there any processes
> apart from these process, what are the mandotory process come along with
> the
> postgres server and how many sleeping processes are there.

Well, there is a bunch of processes started at the beginning, and then
there is one backend process for each connection (see the postgresql.conf
how many connections are allowed in your case).

> Few important question about Postgres Architecture
> ==
> 1. When does temp_buffer comes into existence in a database session ( like
> when shared_buffer completely filled or any wal_buffer filled) ?

AFAIK Those are completely different buffers I.

Shared buffers are used as a "shared cache" for all the backends /
sessions. Wal_buffers are used when writing data to the write-ahead-log
(transaction log) and are shared by all backends just like the shared
buffers.

Temp_buffers on the other hand are used to access temporary tables - so
they serve s a completely different purpose and are session-specific. Each
session may consume the given amount of memory.

> 2. What is process array in shared memory?

No, idea (not a PostgreSQL hacker) but I'd guess it has something to do
with the backends (list of backends).

> 3. maintenance_work_mem is used for vacuuming(does this memory allocated
> if
> autovacuum is off)?

Yes, it's used for vacuuming (and many other things related to
maintenance). AFAIK the amount of memory is 'upper limit' and does not
mean the autovacuum will consume that.

Anyway disabling the autovacuum is a bad idea, just as using not enough
memory.

> 4. As oracle,  Postgres doesnt have any undo_tablespace, for rollback
> transaction. But it is handled in BEGIN /END block. So question is where
> the
> transaction data is stored.

Oracle uses a completely different implementation of MVCC architecture. It
overwrites the data and then uses rollback segments to provide 'previous
versions' to running transactions etc.

PostgreSQL does not overwrite the data - it just creates a copy of the row
and then decides which version should each session see (depending on the
transaction IDs etc.). So it does not need to do rollbacks the way Oracle
does, but it has to remove stale copies of the rows (such that no running
transaction can see) - that's why there is VACUUM.

Regards
Tomas


-- 
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] data type to store a lot of text and tables of data

[Karina Guardado]

Thank you very much  for your help and so soon answears. I will try using
the solutions you suggest.

best regards,

karina

On Wed, Mar 24, 2010 at 4:44 PM, Raymond O'Donnell  wrote:

> On 24/03/2010 22:39, Karina Guardado wrote:
> > You know I need to design a table where some attributes have a data type
> > that allow me to store  text mixed with tables and other information. I
> > use PHP code to get the information and store it in to the database so I
> > wonder if this is posible to do. I found in the link you provided me
> > that  If character varying is used without length specifier, the type
> > accepts strings of any size. So this is useful for me when only text has
> > to be stored but when the user wants to store information mixed with
> > tables like the following :
>
> I suppose it depends on what the format of the data is the example
> you posted is HTML, which is pure text, so could be stored in a VARCHAR
> or TEXT column.
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>

Re: [GENERAL] find a string contained in an attribute

[Karina Guardado]

Do you have a link where I can read more about this because is not easy to
understand.

Thanks for your help,

karina

On Thu, Mar 25, 2010 at 1:34 AM, Pavel Stehule wrote:

> 2010/3/24 Szymon Guz :
> > 2010/3/24 Karina Guardado 
> >>
> >> Hi,
> >>
> >> I want to know if it is possible to search for a string of characters
> >> inside an attribute for example I have the following table and values
> >>
> >> cod_unidad | nombre_uni
> >> +-
> >>   1 | Facultad de Ciencias Naturales y Matemática
> >>   2 | Facultad de Ciencias Humanidades
> >>   3 | Facultad de Ingeniería
> >>   4 | Facultad de Agronomía
> >>   5 | Oficinas Centrales
> >>   6 | test
> >>
> >>
> >> I want to retrieve all the rows where I find the word Ciencias for
> >> example. Is there a function or with select is possible to do it?
> >>
> >> thanks in advance,
> >>
> >> karina
> >> El Salvador, Centroamerica
> >
> > For example something like this should work:
> > SELECT * FROM table WHERE nombre_uni like '%Ciencias%';
> > more you can find
> > here:
> http://www.postgresql.org/docs/8.4/interactive/functions-matching.html
> > regards
> > Szymon Guz
>
> better to use a fulltext it is much faster on tables larger than small
>
> create index foo on tab using gin(to_tsvector('simple', nombre_uni))
>
> select * from tab where to_tsvector('simple', nombre_uni) @@
> to_tsquery('simple', 'Ciencias');
>
> Regards
> Pavel Stehule
>

Re: [GENERAL] find a string contained in an attribute

[Pavel Stehule]

http://www.postgresql.org/docs/8.4/static/textsearch.html

Regards
Pavel Stehule


2010/3/25 Karina Guardado :
> Do you have a link where I can read more about this because is not easy to
> understand.
>
> Thanks for your help,
>
> karina
>
> On Thu, Mar 25, 2010 at 1:34 AM, Pavel Stehule 
> wrote:
>>
>> 2010/3/24 Szymon Guz :
>> > 2010/3/24 Karina Guardado 
>> >>
>> >> Hi,
>> >>
>> >> I want to know if it is possible to search for a string of characters
>> >> inside an attribute for example I have the following table and values
>> >>
>> >> cod_unidad | nombre_uni
>> >> +-
>> >>   1 | Facultad de Ciencias Naturales y Matemática
>> >>   2 | Facultad de Ciencias Humanidades
>> >>   3 | Facultad de Ingeniería
>> >>   4 | Facultad de Agronomía
>> >>   5 | Oficinas Centrales
>> >>   6 | test
>> >>
>> >>
>> >> I want to retrieve all the rows where I find the word Ciencias for
>> >> example. Is there a function or with select is possible to do it?
>> >>
>> >> thanks in advance,
>> >>
>> >> karina
>> >> El Salvador, Centroamerica
>> >
>> > For example something like this should work:
>> > SELECT * FROM table WHERE nombre_uni like '%Ciencias%';
>> > more you can find
>> >
>> > here: http://www.postgresql.org/docs/8.4/interactive/functions-matching.html
>> > regards
>> > Szymon Guz
>>
>> better to use a fulltext it is much faster on tables larger than small
>>
>> create index foo on tab using gin(to_tsvector('simple', nombre_uni))
>>
>> select * from tab where to_tsvector('simple', nombre_uni) @@
>> to_tsquery('simple', 'Ciencias');
>>
>> Regards
>> Pavel Stehule
>
>

-- 
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] How many Mandatory Process are there in Postgres

[Tadipathri Raghu]

Hi  Tomas,

Thank you for the reply.


> Well, there is a bunch of processes started at the beginning, and then
> there is one backend process for each connection (see the postgresql.conf
> how many connections are allowed in your case).
>
I do agree with you, that there would be bunch of process. Can you describe
any?


> Temp_buffers on the other hand are used to access temporary tables - so
> they serve s a completely different purpose and are session-specific. Each
> session may consume the given amount of memory.


So, what happen when the shared_buffer is filled completely, where it does
its operation.


> Yes, it's used for vacuuming (and many other things related to
> maintenance). AFAIK the amount of memory is 'upper limit' and does not
> mean the autovacuum will consume that.
>
> Anyway disabling the autovacuum is a bad idea, just as using not enough
> memory.


My question is does maintenance_work_mem is occupied or allocated, even the
autovacuum is off.


> Oracle uses a completely different implementation of MVCC architecture. It
> overwrites the data and then uses rollback segments to provide 'previous
> versions' to running transactions etc.
>
> PostgreSQL does not overwrite the data - it just creates a copy of the row
> and then decides which version should each session see (depending on the
> transaction IDs etc.). So it does not need to do rollbacks the way Oracle
> does, but it has to remove stale copies of the rows (such that no running
> transaction can see) - that's why there is VACUUM.


Here, if you have issued a command pg_start_backup() at that time the
cluster is freezed, and if any transaction takes place before the
pg_stop_backup() issued at that time where the transaction data will be kept
if the undo's are not there.

Regards
Raghav

2010/3/25 

> > Hi All,
> >
> > When we start the postgres server, the writer process, wal process,
> > postmaster, autovacuum ( if autovacuum is on), stats collector will come
> > into picture as mandotory process. My question is, is there any processes
> > apart from these process, what are the mandotory process come along with
> > the
> > postgres server and how many sleeping processes are there.
>
> Well, there is a bunch of processes started at the beginning, and then
> there is one backend process for each connection (see the postgresql.conf
> how many connections are allowed in your case).
>
> > Few important question about Postgres Architecture
> > ==
> > 1. When does temp_buffer comes into existence in a database session (
> like
> > when shared_buffer completely filled or any wal_buffer filled) ?
>
> AFAIK Those are completely different buffers I.
>
> Shared buffers are used as a "shared cache" for all the backends /
> sessions. Wal_buffers are used when writing data to the write-ahead-log
> (transaction log) and are shared by all backends just like the shared
> buffers.
>
> Temp_buffers on the other hand are used to access temporary tables - so
> they serve s a completely different purpose and are session-specific. Each
> session may consume the given amount of memory.
>
> > 2. What is process array in shared memory?
>
> No, idea (not a PostgreSQL hacker) but I'd guess it has something to do
> with the backends (list of backends).
>
> > 3. maintenance_work_mem is used for vacuuming(does this memory allocated
> > if
> > autovacuum is off)?
>
> Yes, it's used for vacuuming (and many other things related to
> maintenance). AFAIK the amount of memory is 'upper limit' and does not
> mean the autovacuum will consume that.
>
> Anyway disabling the autovacuum is a bad idea, just as using not enough
> memory.
>
> > 4. As oracle,  Postgres doesnt have any undo_tablespace, for rollback
> > transaction. But it is handled in BEGIN /END block. So question is where
> > the
> > transaction data is stored.
>
> Oracle uses a completely different implementation of MVCC architecture. It
> overwrites the data and then uses rollback segments to provide 'previous
> versions' to running transactions etc.
>
> PostgreSQL does not overwrite the data - it just creates a copy of the row
> and then decides which version should each session see (depending on the
> transaction IDs etc.). So it does not need to do rollbacks the way Oracle
> does, but it has to remove stale copies of the rows (such that no running
> transaction can see) - that's why there is VACUUM.
>
> Regards
> Tomas
>
>

Re: [GENERAL] How many Mandatory Process are there in Postgres

[tv]

> Hi  Tomas,
>
> Thank you for the reply.
>
>
>> Well, there is a bunch of processes started at the beginning, and then
>> there is one backend process for each connection (see the
>> postgresql.conf
>> how many connections are allowed in your case).
>>
> I do agree with you, that there would be bunch of process. Can you
> describe
> any?

The names of the processes are quite descriptive I guess - just start the
database and use 'ps ax' command. And then use the names to search the
docs, there's a plenty of details available there.

>> Temp_buffers on the other hand are used to access temporary tables - so
>> they serve s a completely different purpose and are session-specific.
>> Each
>> session may consume the given amount of memory.
>
>
> So, what happen when the shared_buffer is filled completely, where it does
> its operation.

See this:
http://wiki.postgresql.org/wiki/User:Gsmith#How_is_the_shared_buffer_cache_organized

Shared buffers is a LRU cache, i.e. when it's full and a new buffer is
requested, some of the 'oldest' buffers are removed.

>
>
>> Yes, it's used for vacuuming (and many other things related to
>> maintenance). AFAIK the amount of memory is 'upper limit' and does not
>> mean the autovacuum will consume that.
>>
>> Anyway disabling the autovacuum is a bad idea, just as using not enough
>> memory.
>
>
> My question is does maintenance_work_mem is occupied or allocated, even
> the
> autovacuum is off.

No. As I wrote the maintenance_work_mem is the maximum amount of memory
the vacuum may consume, so if it is not running the memory is not
allocated. BTW you could find this out simply by starting the server with
enabled / disabled autovacuum and comparing the amount of memory consumed.

>> Oracle uses a completely different implementation of MVCC architecture.
>> It
>> overwrites the data and then uses rollback segments to provide 'previous
>> versions' to running transactions etc.
>>
>> PostgreSQL does not overwrite the data - it just creates a copy of the
>> row
>> and then decides which version should each session see (depending on the
>> transaction IDs etc.). So it does not need to do rollbacks the way
>> Oracle
>> does, but it has to remove stale copies of the rows (such that no
>> running
>> transaction can see) - that's why there is VACUUM.
>
>
> Here, if you have issued a command pg_start_backup() at that time the
> cluster is freezed, and if any transaction takes place before the
> pg_stop_backup() issued at that time where the transaction data will be
> kept
> if the undo's are not there.

What do you mean by 'freezed'? The cluster operates normally, the
pg_start_backup() just creates a backup label (and performs a checkpoint),
but that's not a problem. OK, there could be a performance decrease
because of full page writes, but the data will be processed as if there is
no backup running. PostgreSQL does not need the checkpoints to perform
backup.

Tomas


-- 
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] tables getting bloated

[akp geek]

Thanks for the help

2010/3/18 Craig Ringer 

> On 18/03/2010 9:48 PM, akp geek wrote:
>
>> I have job that does the vacuum full every day for those 2 tables and
>> also for the database. By the end of the day, the get bloated.
>>
>
>
> http://wiki.postgresql.org/wiki/Introduction_to_VACUUM%2C_ANALYZE%2C_EXPLAIN%2C_and_COUNT
>
> http://wiki.postgresql.org/wiki/VACUUM_FULL
>
> --
> Craig Ringer
>

Re: [GENERAL] How many Mandatory Process are there in Postgres

[Scott Mead]

2010/3/25 




> >> Oracle uses a completely different implementation of MVCC architecture.
> >> It
> >> overwrites the data and then uses rollback segments to provide 'previous
> >> versions' to running transactions etc.
> >>
> >> PostgreSQL does not overwrite the data - it just creates a copy of the
> >> row
> >> and then decides which version should each session see (depending on the
> >> transaction IDs etc.). So it does not need to do rollbacks the way
> >> Oracle
> >> does, but it has to remove stale copies of the rows (such that no
> >> running
> >> transaction can see) - that's why there is VACUUM.
> >
> >
> > Here, if you have issued a command pg_start_backup() at that time the
> > cluster is freezed, and if any transaction takes place before the
> > pg_stop_backup() issued at that time where the transaction data will be
> > kept
> > if the undo's are not there.
>
> What do you mean by 'freezed'? The cluster operates normally, the
> pg_start_backup() just creates a backup label (and performs a checkpoint),
> but that's not a problem. OK, there could be a performance decrease
> because of full page writes, but the data will be processed as if there is
> no backup running. PostgreSQL does not need the checkpoints to perform
> backup.
>

The 'UNDO' data is just kept in the main data files.  Then, based on what
your xid is, and the xmin / xmax on each row, it's either visible or not.
 That's what vacuum does, clean up the rows that are for 'undo' and can
never been seen anymore.  The REDO itself is kept in the transaction logs.

Like Thomas says, the pg_start_backup() just creates a label that tells the
restoring database what pg_xlog record to start with when you 'restore' your
data.  The 'UNDO' type of data (just the old rows actually) is just kept in
the main table until vacuum nukes 'em.

--Scott

[GENERAL] Does anyone use in ram postgres database?

[Chris Barnes]

 

 

  We are testing in memory postgres database and have questions about 
configuring the ram mount point and whether there is great gains in setting it 
up this way? Are there any considerations for postgres?

 

  If you have experience, can you please give us some ideas on how you have 
accomplished this?

 

Cheers,

 

Chris Barnes

 

 
  
_
Stay in touch.
http://go.microsoft.com/?linkid=9712959

Re: [GENERAL] Large index operation crashes postgres

[Frans Hals]

Tom,

ran a CREATE INDEX to the gdb operated postmaster.
Nothing new due to missing debugging libraries, so this might not help:

Reading symbols from /usr/bin/postmaster...(no debugging symbols found)...done.
Missing separate debuginfos, use: debuginfo-install
postgresql-server-8.3.9-1PGDG.f12.x86_64
(gdb) run
Starting program: /usr/bin/postmaster
[Thread debugging using libthread_db enabled]
Detaching after fork from child process 2869.
LOG:  database system was interrupted; last known up at 2010-03-24 05:51:12 PDT
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at CF/D482490
LOG:  unexpected pageaddr CE/AD49 in log file 207, segment 13,
offset 4784128
LOG:  redo done at CF/D48FB80
Detaching after fork from child process 2870.
Detaching after fork from child process 2871.
Detaching after fork from child process 2872.
LOG:  database system is ready to accept connections
Detaching after fork from child process 2996.
Detaching after fork from child process 3682.
Detaching after fork from child process 3685.
Detaching after fork from child process 3687.
Detaching after fork from child process 3688.
Detaching after fork from child process 3690.
Detaching after fork from child process 3691.
Detaching after fork from child process 3696.
Detaching after fork from child process 3698.
Detaching after fork from child process 3702.
Detaching after fork from child process 3706.
Detaching after fork from child process 3710.
LOG:  background writer process (PID 2870) was terminated by signal 9: Killed
LOG:  terminating any other active server processes
LOG:  statistics collector process (PID 2872) was terminated by signal 9: Killed
Detaching after fork from child process 5595.
FATAL:  the database system is in recovery mode

Is there anything meaningful for you?
Does it makes sense to install the debuginfos to catch the problem? If
yes, I may do so.

Paul asked me, to check another function of the postgis set for a
memory leak. I will try this now.

Thanks & regards
Frans

2010/3/24 Tom Lane :

> Can you provide a stack trace from the crash?
>

-- 
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] Does anyone use in ram postgres database?

[Merlin Moncure]

On Thu, Mar 25, 2010 at 2:42 PM, Chris Barnes
 wrote:
>   We are testing in memory postgres database and have questions about
> configuring the ram mount point and whether there is great gains in setting
> it up this way? Are there any considerations for postgres?

There is very little reason to do this.  both postgres and the
operating system cache frequently used pages in memory already and
they are pretty smart about it -- this leaves more memory for
temporary demands like sorts, indexes, large result sets, etc.  It's
usually just as good to simply set fsync=off on the database in
scenarios where you are ok with data loss following a crash and the
system is performance critical.

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] Does anyone use in ram postgres database?

[Scott Marlowe]

On Thu, Mar 25, 2010 at 12:42 PM, Chris Barnes
 wrote:
>
>
>   We are testing in memory postgres database and have questions about
> configuring the ram mount point and whether there is great gains in setting
> it up this way? Are there any considerations for postgres?
>
>   If you have experience, can you please give us some ideas on how you have
> accomplished this?

These questions always get the first question back, what are you
trying to accomplish?  Different objectives will have different
answers.

A common answer to these issues is to put some small but heavily used
tables into ram, and leave the rest on the hard drive.  Easy enough:

(as pg unix user)
mkdir /dev/shm/pgram
psql postgres
create tablespace test location '/dev/shm/pgram';
set temp_tablespaces test;

And now all temp tables are in ram.

Now, if your pg_xlog directory is a problem, then you either need
bigger faster hard drives, or your data is more transient in nature
and you can recreate it and you put the whole db into RAM.

Note that the query planner wasn't designed with RAM as the storage
space for pg, so it might make some bad decisions until you adjust
postgresql.conf to stop that.  and then it still might make some bad
decisions.

-- 
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] Large index operation crashes postgres

[Frans Hals]

Paul,

I have started the operation right now after a fresh reboot of the machine.

 Please give me some time to collect the results...

Thanks & regards
Frans

2010/3/25 Paul Ramsey :
> If you build an index, or try to update a column, using just the
>
> ST_X(ST_Centroid())
>
> without all the extra pl/pgsql wrapping, do you still see the memory
> footprint grow substantially?
>
> P.

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

[GENERAL] Performing FETCH ALL from a SCROLL CURSOR failing to return results

[Eliot Gable]

This issue is on PostgreSQL 8.4.1 under CentOS 5.3 x86_64.

I have a scroll cursor defined like so:

source_host SCROLL CURSOR IS
SELECT ;

Inside my PL/PGSQL stored procedure, I am opening the cursor like so:

OPEN source_host;
FETCH source_host INTO src_host;
result.source_host_refcurs := source_host;
...
blah blah blah

RETURN result;

Then, I execute the stored procedure like this:

SELECT * FROM MyStoredProc(blah);
FETCH ALL FROM source_host;

The stored procedure returns a complex data type (result) with a refcursor
set up as source_host. When I use the "FETCH ALL" syntax, I get no results.
However, if I use any of these, I get the one and only record that is
returned:

FETCH FIRST FROM source_host;
FETCH LAST FROM source_host;
FETCH ABSOLUTE 1 FROM source_host;

Any of these fail:

FETCH NEXT
FETCH PRIOR
FETCH RELATIVE x where x is any number
FETCH x where x is any number
FETCH ALL
FETCH FORWARD
FETCH FORWARD x where x is any number
FETCH FORWARD ALL
FETCH BACKWARD
FETCH BACKWARD x where x is any number
FETCH BACKWARD ALL

Now, if I comment out the 'FETCH source_host INTO src_host' line inside the
stored procedure, then ALL of these work:

FETCH FIRST
FETCH LAST
FETCH ABSOLUTE x
FETCH RELATIVE x
FETCH NEXT
FETCH ALL
FETCH FORWARD
FETCH FORWARD x
FETCH FORWARD ALL
FETCH x

I have attempted to perform a MOVE FIRST aftering doing the 'FETCH
source_host INTO src_host' line, as well as MOVE LAST, MOVE ABSOLUTE 1, etc.
No attempt at doing a MOVE allows the FETCH ALL and the like to work. Only
FETCH FIRST, FETCH LAST, and FETCH ABSOLUTE seem to work after I have
touched the cursor inside the stored procedure. In fact, I can remove the
'FETCH source_host INTO src_host' line and replace it with a MOVE statement
and it results in the same problem.

I absolutely need to have FETCH ALL working. I don't care about anything
else other than FETCH ALL. I actually have about 10 cursors that are
returned like this from the stored procedure, and most of them have several
dozen records that need to be retrieved. I execute a single transaction
where I run the stored procedure and fetch all results all at once. This was
working just fine a couple of days ago. Not sure what broke.

If anyone has any ideas on what might be going wrong here, I would really
appreciate some assistance.

Thanks in advance.


-- 
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero

Re: [GENERAL] Performing FETCH ALL from a SCROLL CURSOR failing to return results

[Tom Lane]

Eliot Gable  writes:
> Inside my PL/PGSQL stored procedure, I am opening the cursor like so:

> OPEN source_host;
> FETCH source_host INTO src_host;

So you already fetched the one row in the cursor.  FETCH ALL really
means "fetch all the rest of the rows", which is why it returns nothing.
The other variants you mention will reposition the cursor so they can
re-read the row.

> I absolutely need to have FETCH ALL working.

It is working.  What you're not doing correctly is repositioning the
cursor to before the first row.  Try MOVE BACKWARDS ALL.

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] Get the list of permissions/privileges on schema

[John R Pierce]

dipti shah wrote:
Thanks a lot guys but I am not looking for security definer function. 
I know it. My requirements are very complicated and I have to nailed 
down the stuffs by storing schema permissions somewhere, execute my 
store procedure, and restored the stored schema permissions. Like this 
I would make sure that thogh my store procedure manipulates schema 
permissions, at the end, users will have their permissions intact.
 


thats totally the wrong way to do things in SQL.



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

[GENERAL] Geoffrey Gowey wants to stay in touch on LinkedIn

[Geoffrey Gowey]

LinkedIn
Geoffrey Gowey requested to add you as a connection on LinkedIn:
--

Andrew,

I'd like to add you to my professional network on LinkedIn.

- Geoffrey Gowey

Accept invitation from Geoffrey Gowey
http://www.linkedin.com/e/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I1918266464_2/1BpC5vrmRLoRZcjkkZt5YCpnlOt3RApnhMpmdzgmhxrSNBszYOnPgSd3oSczwNej59bPlLhD9he7EQbP0QczoPc3sTcj4LrCBxbOYWrSlI/EML_comm_afe/

View invitation from Geoffrey Gowey
http://www.linkedin.com/e/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I1918266464_2/39vd3oQdzoOe34VckALqnpPbOYWrSlI/svi/
 
--

DID YOU KNOW you can showcase your professional knowledge on LinkedIn to 
receive job/consulting offers and enhance your professional reputation? Posting 
replies to questions on LinkedIn Answers puts you in front of the world's 
professional community.
http://www.linkedin.com/e/abq/inv-24/

 
--
(c) 2010, LinkedIn Corporation

[GENERAL] Problem with Memory Leak

[Pete Kay]

Hi

I am having memory leaking problem and here is the result shown from valgrind:


==1706== 1,270 bytes in 5 blocks are still reachable in loss record 353 of 426
==1706==at 0x4C20809: malloc (vg_replace_malloc.c:149)
==1706==by 0x5D3F431: strdup (in /lib64/libc-2.5.so)
==1706==by 0x109484E0: PQsendQuery (in /usr/lib64/libpq.so.5.2)


Here is my source code:

static PGresult *get_pg_result(char *sql) {


PGresult *res=NULL;
PGresult *final_res=NULL;
PGconn* conn = NULL;

int ret = 0;

db_con_t* connection_node = get_connection();


if (connection_node == NULL) {
free(sql);
return  NULL;
}




conn = connection_node->conn;
if (PQstatus(conn) != CONNECTION_OK){
PQfinish(conn);
return NULL;
}

ret = PQsendQuery(conn, sql);
 if (1 != ret) {


switch_safe_free(sql);
release_connection(connection_node);
return NULL;
}

while ((res = PQgetResult(conn))) {


final_res = res;
}







free(sql);
return final_res;
}

-- 
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] Problem with Memory Leak

[Tom Lane]

Pete Kay  writes:
> I am having memory leaking problem and here is the result shown from valgrind:

> ==1706== 1,270 bytes in 5 blocks are still reachable in loss record 353 of 426
> ==1706==at 0x4C20809: malloc (vg_replace_malloc.c:149)
> ==1706==by 0x5D3F431: strdup (in /lib64/libc-2.5.so)
> ==1706==by 0x109484E0: PQsendQuery (in /usr/lib64/libpq.so.5.2)

Well, if that trace is to be believed, it must be talking about
conn->last_query.  Which will be freed when the connection is closed
(ie PQfinish).   It looks to me like you might need to pay more
attention to closing things when you're done with them.  The logic
you quoted seems pretty full of holes: it's freeing the argument "sql"
in only some code paths, it leaves connection_node->conn as a dangling
non-null pointer in the bad-PQstatus path, it leaks non-last PGresults;
and you haven't even shown us where the connection is supposed to get
cleaned up in non-failure cases.

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] How many Mandatory Process are there in Postgres

[Tadipathri Raghu]

>
> Hi Scott, Thomas,

Thank you for the update.


>  >> Oracle uses a completely different implementation of MVCC
> architecture.
> >> It
> >> overwrites the data and then uses rollback segments to provide 'previous
> >> versions' to running transactions etc.
> >>
> >> PostgreSQL does not overwrite the data - it just creates a copy of the
> >> row
> >> and then decides which version should each session see (depending on the
> >> transaction IDs etc.). So it does not need to do rollbacks the way
> >> Oracle
> >> does, but it has to remove stale copies of the rows (such that no
> >> running
> >> transaction can see) - that's why there is VACUUM.
> >
> >
> > Here, if you have issued a command pg_start_backup() at that time the
> > cluster is freezed, and if any transaction takes place before the
> > pg_stop_backup() issued at that time where the transaction data will be
> > kept
> > if the undo's are not there.
>
>
> What do you mean by 'freezed'? The cluster operates normally, the
> pg_start_backup() just creates a backup label (and performs a checkpoint),
> but that's not a problem. OK, there could be a performance decrease
> because of full page writes, but the data will be processed as if there is
> no backup running. PostgreSQL does not need the checkpoints to perform
> backup.
>

The 'UNDO' data is just kept in the main data files.  Then, based on what
> your xid is, and the xmin / xmax on each row, it's either visible or not.
>  That's what vacuum does, clean up the rows that are for 'undo' and can
> never been seen anymore.  The REDO itself is kept in the transaction logs.



> Like Thomas says, the pg_start_backup() just creates a label that tells the
> restoring database what pg_xlog record to start with when you 'restore' your
> data.  The 'UNDO' type of data (just the old rows actually) is just kept in
> the main table until vacuum nukes 'em.


--Scott
Q1. Does vacuum process come into existence even you turn off the
autovacuum.? What is the main work for vacuum process, to the clean the
buffers or work only when autovacuum in on and to clean up the rows that are
'undo'
Q2. Do you mean to say there is a vacuum process which will clean up the
buffers that are for 'undo'?
Q3. Fine, if the vacuum is off then your data files will hold the undo data
also in it?


> See this:
>
> http://wiki.postgresql.org/wiki/User:Gsmith#How_is_the_shared_buffer_cache_organized

Shared buffers is a LRU cache, i.e. when it's full and a new buffer is
> requested, some of the 'oldest' buffers are removed.


Suppose, if there is a big transaction going on , in between of the
transaction shared_buffer got filled even after oldest buffers are removed,
at that time where does operation performs to finish up the transaction. As
it is a shared buffer every clients will be accessing this and even they
demand for the space for there operation too.

Regards
Raghav


On Thu, Mar 25, 2010 at 10:49 PM, Scott Mead
wrote:

>  2010/3/25 
>
> 
>
>
>> >> Oracle uses a completely different implementation of MVCC architecture.
>> >> It
>> >> overwrites the data and then uses rollback segments to provide
>> 'previous
>> >> versions' to running transactions etc.
>> >>
>> >> PostgreSQL does not overwrite the data - it just creates a copy of the
>> >> row
>> >> and then decides which version should each session see (depending on
>> the
>> >> transaction IDs etc.). So it does not need to do rollbacks the way
>> >> Oracle
>> >> does, but it has to remove stale copies of the rows (such that no
>> >> running
>> >> transaction can see) - that's why there is VACUUM.
>> >
>> >
>> > Here, if you have issued a command pg_start_backup() at that time the
>> > cluster is freezed, and if any transaction takes place before the
>> > pg_stop_backup() issued at that time where the transaction data will be
>> > kept
>> > if the undo's are not there.
>>
>> What do you mean by 'freezed'? The cluster operates normally, the
>> pg_start_backup() just creates a backup label (and performs a checkpoint),
>> but that's not a problem. OK, there could be a performance decrease
>> because of full page writes, but the data will be processed as if there is
>> no backup running. PostgreSQL does not need the checkpoints to perform
>> backup.
>>
>
> The 'UNDO' data is just kept in the main data files.  Then, based on what
> your xid is, and the xmin / xmax on each row, it's either visible or not.
>  That's what vacuum does, clean up the rows that are for 'undo' and can
> never been seen anymore.  The REDO itself is kept in the transaction logs.
>
> Like Thomas says, the pg_start_backup() just creates a label that tells the
> restoring database what pg_xlog record to start with when you 'restore' your
> data.  The 'UNDO' type of data (just the old rows actually) is just kept in
> the main table until vacuum nukes 'em.
>
> --Scott
>

Re: [GENERAL] How many Mandatory Process are there in Postgres

[Scott Marlowe]

On Thu, Mar 25, 2010 at 9:03 PM, Tadipathri Raghu  wrote:
> Hi Scott, Thomas,
>
> Thank you for the update.
>
>>
>> >> Oracle uses a completely different implementation of MVCC architecture.
>> >> It
>> >> overwrites the data and then uses rollback segments to provide
>> >> 'previous
>> >> versions' to running transactions etc.
>> >>
>> >> PostgreSQL does not overwrite the data - it just creates a copy of the
>> >> row
>> >> and then decides which version should each session see (depending on
>> >> the
>> >> transaction IDs etc.). So it does not need to do rollbacks the way
>> >> Oracle
>> >> does, but it has to remove stale copies of the rows (such that no
>> >> running
>> >> transaction can see) - that's why there is VACUUM.
>> >
>> >
>> > Here, if you have issued a command pg_start_backup() at that time the
>> > cluster is freezed, and if any transaction takes place before the
>> > pg_stop_backup() issued at that time where the transaction data will be
>> > kept
>> > if the undo's are not there.
>>
>>
>> What do you mean by 'freezed'? The cluster operates normally, the
>> pg_start_backup() just creates a backup label (and performs a checkpoint),
>> but that's not a problem. OK, there could be a performance decrease
>> because of full page writes, but the data will be processed as if there is
>> no backup running. PostgreSQL does not need the checkpoints to perform
>> backup.
>
>> The 'UNDO' data is just kept in the main data files.  Then, based on what
>> your xid is, and the xmin / xmax on each row, it's either visible or not.
>>  That's what vacuum does, clean up the rows that are for 'undo' and can
>> never been seen anymore.  The REDO itself is kept in the transaction logs.
>
>
>>
>> Like Thomas says, the pg_start_backup() just creates a label that tells
>> the restoring database what pg_xlog record to start with when you 'restore'
>> your data.  The 'UNDO' type of data (just the old rows actually) is just
>> kept in the main table until vacuum nukes 'em.
>
> --Scott
> Q1. Does vacuum process come into existence even you turn off the
> autovacuum.? What is the main work for vacuum process, to the clean the
> buffers or work only when autovacuum in on and to clean up the rows that are
> 'undo'

1a: Yes, if you are approaching transaction id wraparound (txid wrap)
then autovacuum will crank up a vacuum to fix that situation whether
you like it or not.  The alternative is db shutdown.
1b: The main job of vacuum is to free dead tuples, which postgresql
accumulates as it updates or deletes tuples. When you have several old
versions of a tuple to collect, vacuum recycles the space and makes it
available to other updates to now use instead of allocating on the end
of the relation.

> Q2. Do you mean to say there is a vacuum process which will clean up the
> buffers that are for 'undo'?

No buffers for undo.  It's all stored on disc, ready for instant
access.  Try it on a test db.  begin; load a table with 1M rows;
rollback; sub second response.  A million changes just disappeared
instantly.  And now you've got 1M dead tuples in that table.  Vacuum's
job is to make them available for re-use.  But they're all in one big
consecutive file so the performance is actually pretty good as the
table repopulates, especially if it'll get back up to 1M records again
in the near future.  As long as it got vacuumed.

> Q3. Fine, if the vacuum is off then your data files will hold the undo data
> also in it?

Yep.  Until a forced vacuum due to txid wrap occurs.

-- 
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] Problem with Memory Leak

[Pete Kay]

Hi,
I am setting up a connection pooling obj to pool a bunch of
PGConnection object.  That is why I am not closing the PGConn object
when the query is done.

Is that the right way to do it?

thanks,
Pete

On Fri, Mar 26, 2010 at 10:54 AM, Tom Lane  wrote:
> Pete Kay  writes:
>> I am having memory leaking problem and here is the result shown from 
>> valgrind:
>
>> ==1706== 1,270 bytes in 5 blocks are still reachable in loss record 353 of 
>> 426
>> ==1706==    at 0x4C20809: malloc (vg_replace_malloc.c:149)
>> ==1706==    by 0x5D3F431: strdup (in /lib64/libc-2.5.so)
>> ==1706==    by 0x109484E0: PQsendQuery (in /usr/lib64/libpq.so.5.2)
>
> Well, if that trace is to be believed, it must be talking about
> conn->last_query.  Which will be freed when the connection is closed
> (ie PQfinish).   It looks to me like you might need to pay more
> attention to closing things when you're done with them.  The logic
> you quoted seems pretty full of holes: it's freeing the argument "sql"
> in only some code paths, it leaves connection_node->conn as a dangling
> non-null pointer in the bad-PQstatus path, it leaks non-last PGresults;
> and you haven't even shown us where the connection is supposed to get
> cleaned up in non-failure cases.
>
>                        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] How to retrieve List of Tables in a Database using...

[Yogi Yang 007]

Hello,

Thanks  everyone for your help.


I really appreciate it.

Yogi Yang

--
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] Problem with Memory Leak

[Tom Lane]

Pete Kay  writes:
> I am setting up a connection pooling obj to pool a bunch of
> PGConnection object.  That is why I am not closing the PGConn object
> when the query is done.

> Is that the right way to do it?

Well, that's fine, but you should not be complaining about some memory
associated with the PGconn not being reclaimed yet in that state.

I guess the context we are missing is why is it you are worried about
valgrind reporting a memory leak of a kilobyte or so?

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