Re: [GENERAL] need suggestion

Thanks a lot. I didn't know that type of log existed :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/need-suggestion-tp5728154p5728169.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] need suggestion

kostika gorica wrote: > I have an application already running, accessing the database (postgresql > 8.1). I need a solution, that prints (on a file, or somehow) the queries > being executed every time the app accesses the database. > Is there any ON DATABASE ACCESS trigger ?! > Any idea how that ca

[GENERAL] need suggestion

Hello, I have an application already running, accessing the database (postgresql 8.1). I need a solution, that prints (on a file, or somehow) the queries being executed every time the app accesses the database. Is there any ON DATABASE ACCESS trigger ?! Any idea how that can be done? Thanks in

Re: [GENERAL] Need suggestion

On Fri, Jun 3, 2011 at 5:09 PM, Esmin Gracic wrote: > another option is using sqlite for storing images. All data is in single > file. (or files if you organize it that way) easier backup etc... you have > some db benefits and retaining solid speed vs file system. Haven't used > this, but seems as

Re: [GENERAL] Need suggestion

*(or files if you organize it that way)* if the problem was so simple, I guess Carl would not have asked the question in the first place. there could be one sqlite db file for each day, week or month (1TB over 365 days). Something like partitioning on date dimension. Actually, sqlite scales well u

Re: [GENERAL] Need suggestion

On 06/03/11 3:09 PM, Esmin Gracic wrote: another option is using sqlite for storing images. All data is in single file. (or files if you organize it that way) easier backup etc... you have some db benefits and retaining solid speed vs file system. Haven't used this, but seems as viable option t

Re: [GENERAL] Need suggestion

another option is using sqlite for storing images. All data is in single file. (or files if you organize it that way) easier backup etc... you have some db benefits and retaining solid speed vs file system. Haven't used this, but seems as viable option to explore. Esmin On Fri, Jun 3, 2011 at 6:3

Re: [GENERAL] Need suggestion

Thanks for the replies, and suggestion from Ognjen, Ben Chobot, John R Pierce, Tomás, and Karsten... I checked the links, and I decided, that I cannot decide :-) because I don't know, how large could be the infrastructure for this. If I store the images, and scanned docus in the database, a radical

Re: [GENERAL] Need suggestion

On Fri, Jun 03, 2011 at 07:15:40AM +0200, to...@tuxteam.de wrote: > but you wouldn't have large blobs of data clobbering your "regular" queries. You would want to write better queries than select * from my_table_with_bytea_column; anyway. > You could pass the scans and pics piecemeal b

Re: [GENERAL] Need suggestion

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Following up on myself: Just stumbled upon this in the hackers mailing list, which might be interesting to you, since it highlights pros & cons of current implementations: Re

Re: [GENERAL] Need suggestion

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, Jun 01, 2011 at 10:08:14AM +0200, Carl von Clausewitz wrote: > Hello Everyone, > > I got a new project, with 100 user in Europe. In this case, I need to handle > production and sales processes an its documentations in PostgreSQL with PHP. Som

Re: [GENERAL] Need suggestion

On 06/02/11 2:19 AM, Ognjen Blagojevic wrote: 1. Files stored on the filesystem: - Small database footprint - Faster backup, export and import 2. Files stored in the database - RDBMS takes care of transactions and ref. int. - Slower backup, export and import but all done in one step - Easier con

Re: [GENERAL] Need suggestion

On Jun 1, 2011, at 1:08 AM, Carl von Clausewitz wrote: > Hello Everyone, > > I got a new project, with 100 user in Europe. In this case, I need to handle > production and sales processes an its documentations in PostgreSQL with PHP. > The load of the sales process is negligible, but every user

Re: [GENERAL] Need suggestion

Carl, Please keep in mind I am not a Postgres expert nor consultant, I'm just sharing my experience. I would also like to hear the opinion of other people who worked on projects with similar database sizes. I would keep all files in the single table -- most probably they will be served to th

Re: [GENERAL] Need suggestion

Dear Ognjen, thank you - that was my idea too, but I've never seen such a workload like this. The docu's (which are not processed by any ocr hopefully) and the pictures are not indexed off course, just some metadatas, which are related to the exact docu, or pic For example: productions_docu1: -se

Re: [GENERAL] Need suggestion

Carl, I don't have experience with that big databases, but I did both solutions, and here are pros of both of them: 1. Files stored on the filesystem: - Small database footprint - Faster backup, export and import 2. Files stored in the database - RDBMS takes care of transactions and ref. int.

[GENERAL] Need suggestion

Hello Everyone, I got a new project, with 100 user in Europe. In this case, I need to handle production and sales processes an its documentations in PostgreSQL with PHP. The load of the sales process is negligible, but every user produces 2 transaction in the production process, with 10-30 scanned

Re: [GENERAL] Need suggestion on how best to update 3 million rows

Alvaro Herrera wrote: > Lincoln Yeoh wrote: > > At 06:32 PM 9/6/2007, Richard Huxton wrote: > > > >> Two other tips for bulk-updates like this: > >> 1. Do as many columns in one go as you can > >> 2. Only update rows that need updating > >> > >> When you've finished, a CLUSTER/VACUUM FULL can be us

Re: [GENERAL] Need suggestion on how best to update 3 million rows

Lincoln Yeoh wrote: > At 06:32 PM 9/6/2007, Richard Huxton wrote: > >> Two other tips for bulk-updates like this: >> 1. Do as many columns in one go as you can >> 2. Only update rows that need updating >> >> When you've finished, a CLUSTER/VACUUM FULL can be useful too. > > How about: make sure you

Re: [GENERAL] Need suggestion on how best to update 3 million rows

At 06:32 PM 9/6/2007, Richard Huxton wrote: Two other tips for bulk-updates like this: 1. Do as many columns in one go as you can 2. Only update rows that need updating When you've finished, a CLUSTER/VACUUM FULL can be useful too. How about: make sure you have enough free space because the t

Re: [GENERAL] Need suggestion on how best to update 3 million rows

On Thu, Sep 06, 2007 at 01:39:51PM +0200, Nis Jørgensen wrote: > Rubbish. From the documentation: hmm .. i'm sorry - i was *sure* about it because we were bitten by something like this lately - apparently it was similiar but not the same. sorry again for misinformation. depesz -- quicksil1er:

Re: [GENERAL] Need suggestion on how best to update 3 million rows

hubert depesz lubaczewski skrev: > On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: >> create index tmp_idx on table(number) where number != trim(number); >> analyze table; >> update table set number = trim(number) where number != trim(number); > > dont use !=. use <>. != does somet

Re: [GENERAL] Need suggestion on how best to update 3 million rows

On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: > create index tmp_idx on table(number) where number != trim(number); > analyze table; > update table set number = trim(number) where number != trim(number); dont use !=. use <>. != does something different, and in fact it is not a re

Re: [GENERAL] Need suggestion on how best to update 3 million rows

Ow Mun Heng wrote: On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote: Maybe there's an English language "issue", or maybe I'm just excessively picky, but using "number" in this context is confusing. My Bad.. hehe.. Then I agree with Alban: update table set number = trim(number); or, if

Re: [GENERAL] Need suggestion on how best to update 3 million rows

On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote: > Maybe there's an English language "issue", or maybe I'm just > excessively picky, but using "number" in this context is confusing. My Bad.. hehe.. > Then I agree with Alban: > update table set number = trim(number); > or, if you need the

Re: [GENERAL] Need suggestion on how best to update 3 million rows

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 04:20, Ow Mun Heng wrote: > On Thu, 2007-09-06 at 04:07 -0500, Ron Johnson wrote: > >> On 09/06/07 01:13, Ow Mun Heng wrote: > >>> update org_column set number = foo.number where foo.unique_id = >>> org_column=unique_id. >> Number? Where

Re: [GENERAL] Need suggestion on how best to update 3 million rows

On Thu, 2007-09-06 at 11:08 +0200, Alban Hertroys wrote: > Ow Mun Heng wrote: > > I found 2 new ways to do this. > > > > option 1 > > --- > > > > create table foo as select unique_id, rtrim(number) as number from foo; > > alter table add primary key... > > create index... > > drop org_table >

Re: [GENERAL] Need suggestion on how best to update 3 million rows

On Thu, 2007-09-06 at 04:07 -0500, Ron Johnson wrote: > On 09/06/07 01:13, Ow Mun Heng wrote: > > update org_column set number = foo.number where foo.unique_id = > > org_column=unique_id. > > Number? Where does "number" come from? Unless you've got weird > field names, that doesn't sound like

Re: [GENERAL] Need suggestion on how best to update 3 million rows

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 01:13, Ow Mun Heng wrote: > I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using > chopblanks) and have ended up with a column where the "space" is being > interpreted as a value. > > eg: > > "ABC " when it should be "A

Re: [GENERAL] Need suggestion on how best to update 3 million rows

Ow Mun Heng wrote: > I found 2 new ways to do this. > > option 1 > --- > > create table foo as select unique_id, rtrim(number) as number from foo; > alter table add primary key... > create index... > drop org_table > alter table rename... > All this is ~10min This only works if you don't hav

Re: [GENERAL] Need suggestion on how best to update 3 million rows

On Thu, 2007-09-06 at 14:13 +0800, Ow Mun Heng wrote: > I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using > chopblanks) and have ended up with a column where the "space" is being > interpreted as a value. > > eg: > > "ABC " when it should be "ABC" > > this is being defined

[GENERAL] Need suggestion on how best to update 3 million rows

I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using chopblanks) and have ended up with a column where the "space" is being interpreted as a value. eg: "ABC " when it should be "ABC" this is being defined as varchar(4) I've already pull the relevent columns with create foo