Re: [GENERAL] delete is getting hung when there is a huge data in table

This delete runs in a single transaction. That means the entire transaction has to complete before you will see anything deleted. Interrupting the transaction simply rolls it back, so nothing is deleted. Tom already pointed out the potential foreign key slowdown, another slowdown may simply be

Re: [GENERAL] delete is getting hung when there is a huge data in table

On Saturday, May 2, 2015, Mitu Verma wrote: > > still this delete operation is not working and not a single row has been > deleted from the table. > > Because of MVCC other sessions are not able to see partial deletions...and as you aluded to knowing the data itself is not actually removed by a d

Re: [GENERAL] delete is getting hung when there is a huge data in table

Mitu Verma writes: > 1. If postgreSQL has some limitations for deletion of large data? Not as such, but you've not given us any details that would permit comment. A reasonably likely bet is that this table is referenced by a foreign key in some other table, and that other table has no index on t

[GENERAL] delete is getting hung when there is a huge data in table

Hi, I am facing an issue with the deletion of huge data. We have a cronscript which is used to delete the data of last 3 months from one of the tables. Data in the table is large (8872597 as you can see the count below) since it is from last 3 months. fm_db_Server3=# select count(*) from auditt

Re: [GENERAL] plpgsql functions organisation

OK, Here is a simple example that shows the difference between using a self contained function and one that calls sub functions. After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that callsubs takes almost TWICE as long to execute as nosub. CREATE

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 03:28 PM, Bill Moran wrote: On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver wrote: On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/02/2015 10:12 AM, Melvin Davidson wrote: AF

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 03:10 PM, Melvin Davidson wrote: Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load the

Re: [GENERAL] plpgsql functions organisation

On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver wrote: > On 05/02/2015 02:07 PM, Jeff Janes wrote: > > On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver > > wrote: > > > > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > > > > AFAIK, you cannot "package

Re: [GENERAL] plpgsql functions organisation

Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load the function from the catalogs, which involves overhe

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/02/2015 10:12 AM, Melvin Davidson wrote: AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a functio

Re: [GENERAL] plpgsql functions organisation

On Sat, 2 May 2015 14:07:31 -0700 Jeff Janes wrote: > On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver > wrote: > > > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > > > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible > >> to > >> call a function from within a function

Re: [GENERAL] plpgsql functions organisation

On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver wrote: > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible >> to >> call a function from within a function. >> >> That being said, I would seriously look at how and why you are

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 10:12 AM, Melvin Davidson wrote: AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not

Re: [GENERAL] plpgsql functions organisation

On 2015-05-02 11:12, Melvin Davidson wrote: > AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to > call a function from within a function. > > That being said, I would seriously look at how and why you are writing your > functions > as functions that call other functions a

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 09:53 AM, Yves Dorfsman wrote: I find my plpgsql functions becomes unreadable very quickly. I want to break them up in smaller functions. What is the best way to organised them? Is there any way to define functions inside functions? When I list functions in psql, I can see them al

Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

Alanoly Andrews wrote: > This table does not occur in the listing produced in step 1 above and so > cannot be edited out Yes, pg_largeobject is not mentioned but the TOC should have an entry named BLOBS, as well as other entries related to each large object. If "BLOBS" is removed or comm

Re: [GENERAL] plpgsql functions organisation

AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not very efficient. Also note that PostgreSQL allows

[GENERAL] plpgsql functions organisation

I find my plpgsql functions becomes unreadable very quickly. I want to break them up in smaller functions. What is the best way to organised them? Is there any way to define functions inside functions? When I list functions in psql, I can see them all at the same level, is there any way to organi

Re: [GENERAL] Success story full text search

Does someone have a success story of using Postgres Full Search Capability with significant data, lets say > 50-100 GB ? This is a recent and very complete article on using Postgres for full-text search: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/ see also the d

Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

I'm not exactly positive, but I believe pg_extractor has an exclude type option that might fit your requirement. You can download it here: https://github.com/omniti-labs/pg_extractor Also note that this is a Python program, so you might even be able to hack some change into it to do what you need

Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

Thanks Vincent. But as I have already mentioned, excluding tables from the dump isn't an option. I want to keep the dump complete, but exclude a table (or more) from the restore. The procedure I have outlined does work well with any table except the ones which have a "large object " column. Ala

Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

Vincent Veyron wrote: > On Fri, 1 May 2015 14:12:08 + > Alanoly Andrews wrote: > > > Hello, > > > > We have a database that has been unloaded using pg_dump. This database has > > a table with a field defined as "lo". When restoring this database to > > another system, we want to avoid lo

Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

On Fri, 1 May 2015 14:12:08 + Alanoly Andrews wrote: > Hello, > > We have a database that has been unloaded using pg_dump. This database has a > table with a field defined as "lo". When restoring this database to another > system, we want to avoid loading this particular table as it is ver

Re: [GENERAL] Success story full text search

Frank Langel wrote: > Does someone have a success story of using Postgres Full Search Capability > with significant data, lets say > 50-100 GB ? How about http://www.postgresql.org/search/?m=1 Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m