Re: [GENERAL] Inserting a new column in between.
Andreas, I am talking about inserting a field or changing their order in the structure itself. In MS Access and SQL Server we have this facility. Some times I need to shift the less important field to the last so that when I query using: Select * from tablename; the un-necessary field gets displayed in the last. However, you I can ignore that field altogether using filtered fields only in Select statement, but still sometimes it is necessary. -- View this message in context: http://www.nabble.com/Inserting-a-new-column-in-between.-tf3286148.html#a9141803 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Inserting a new column in between.
RPK wrote on 25.02.2007 09:44: Select * from tablename; the un-necessary field gets displayed in the last. However, you I can ignore that field altogether using filtered fields only in Select statement, but still sometimes it is necessary. No it's not. SELECT * should be avoided by all means (except for ad-hoc queries). Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Inserting a new column in between.
RPK [EMAIL PROTECTED] schrieb: Andreas, I am talking about inserting a field or changing their order in the structure itself. In MS Access and SQL Server we have this facility. Some times I need to shift the less important field to the last so that when I query using: Select * from tablename; You should don't do that! Why? For instance, you have a table with many rows, including BLOBs. Your applivation needs only a few columns, but you select * returns the whole rows. So, but no problem: test=# create table foo (a int, c int); CREATE TABLE test=*# insert into foo values (1,3); INSERT 0 1 test=*# commit; COMMIT Okay, we have a table with columns a and c, and now i notice i forgot the column b. No problem: test=# begin; BEGIN test=*# create table foo_temp as select a, null::int as b, c from foo; SELECT test=*# drop table foo; DROP TABLE test=*# alter table foo_temp rename to foo; ALTER TABLE test=*# commit; COMMIT test=# select * from foo; a | b | c ---+---+--- 1 | | 3 (1 row) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inserting a new column in between.
Hi RPK My suggestion would be to create a view which presents the columns as you wish. eg CREATE OR REPLACE VIEW tableView AS SELECT a, b, c FROM table Then when you add add a new column, just adjust the view as required. Cheers Noel RPK wrote: Andreas, I am talking about inserting a field or changing their order in the structure itself. In MS Access and SQL Server we have this facility. Some times I need to shift the less important field to the last so that when I query using: Postgres does not allow such ordering as stated in the previous replies. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 How is the Postgres port of the Wikipedia doing this days anyway? Is it in a shape where one would consider it competitive? The port of MediaWiki is going well: it is certainly usable, and is already being used by a number of sites. I would not say it is quite competitive yet as far as being ready to run Wikipedia, as the codebase has a lot of very mysql-specific stuff that has yet to be fixed/coded around. There are also a few lingering bugs, most related to the fact that the MediaWiki on Mysql stores dates as char(14). For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) http://www.mediawiki.org/ - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200702250925 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFF4ZyDvJuQZxSWSsgRA8c6AJ95oTX9YQ38VyPvFyhd54S3rHAZSACgh/tC uqcAmRFuRnMUdPL7sO/eoP0= =w2KL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Perhaps the project should be *gasp* deleted then? ;-) Or is there actual historical information there that someone would be interested in? //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/25/07, Greg Sabino Mullane [EMAIL PROTECTED] wrote: For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Some of us are still using php4 :)
Re: Wikipedia on Postgres (was Re: [GENERAL] postgresql vs mysql)
On 2/25/07, Magnus Hagander [EMAIL PROTECTED] wrote: For the record, anyone using wikipgedia deserves the pain they get: it is deprecated. The latest version of MediaWiki itself is what should now be used: it will detect if you have Postgres upon installation. :) Perhaps the project should be *gasp* deleted then? ;-) Or is there actual historical information there that someone would be interested in? As I said in my other mail, some folks are still using PHP4 -- which is why MediaWiki still maintains the 1.6 branch. I am more than willing to contribute the most recent 1.6.10 codebase w/ PostgreSQL modifications to the foundry. I am actively maintaining my own codebase for my site. I agree with Greg, if you are already using PHP5 then use the MediaWiki distribution, but if your stuck on PHP4 like me then you really don't have a choice other than what is being offered on pgfoundry. :)
[GENERAL] Best way to store and retrieve photo from PostGreSQL
Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? thanks a lot -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Alain Roger [EMAIL PROTECTED] schrieb: Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
See the discussion [GENERAL] Database versus filesystem for storing images earlier on the List. It started at 31 december 2006 and ended 9 januari 2007. It goes trough all/most pro/con arguments for different options. - Joris From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alain Roger Sent: zondag 25 februari 2007 16:57 To: pgsql-general@postgresql.org Subject: [GENERAL] Best way to store and retrieve photo from PostGreSQL Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? thanks a lot -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Andreas Kretschmer wrote: Alain Roger [EMAIL PROTECTED] schrieb: Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) One problem with this approach, is that you move the responsibility for maintaining data integrity from the database, to the application code using the database. You introduce 2 points of failure, where you risk adding the image to the filesystem, without it being added to the database, and the other way around. The same issue appears with deleting/updating. In addition, if there is a large amount of images, you probably do not want to keep all images in the same folder. So you introduce extra complexity in order to maintain a directory structure, and some sort of logic for sorting the images in this structure. -- Tommy ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] complex referential integrity constraints
Actually, what would be really nice is if there were just a button I could push that would make all of my data automatically correct. Can that go into 8.3? Thanks, ...Robert -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Friday, February 23, 2007 9:35 AM To: Alban Hertroys Cc: Robert Haas; David Fetter; pgsql-general@postgresql.org Subject: Re: [GENERAL] complex referential integrity constraints Alban Hertroys wrote: Robert Haas wrote: The idea here is that a wolf can attack a sheep, or a wolf can attack another wolf, but sheep can't attack anything. I suppose I could list each wolf in both the predator and prey tables, but that seems a bit duplicative (and causes other problems). I'm quite certain a wolf is much more likely to attack a sheep than to attack another wolf, and even more unlikely to attack for example a lion. It seems to me that just the fact that it can isn't enough information. It looks like you need weighted constraints; there's 0 chance that a sheep attacks a wolf, but there's 0 chance that a wolf attacks a sheep, 0 chance it attacks a wolf and 0 chance it attacks a lion. The exact numbers will vary, and I have absolutely no idea what they would be like. It probably requires some kind of ranking system that adjusts according to the known animals and their likelihood to attack eachother. Depending on what you're modelling, even this could be too simple -- for example, while a single wolf is unlikely to attack a lion, a pack of wolves have a lot more probability of doing so. Do you keep packs of wolves in your barn? If so, watch your lions. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SQL Question - Using Group By
You could use COUNT() in conjunction with NULLIF: select Type, count(nullif(Active, false)) as Active Count, count(nullif(Active, true)) as Inactive Count, 100 * count(nullif(Active, false)) / count(*) as Active Percent from table_name group by Type On Feb 23, 2:50 pm, Mike [EMAIL PROTECTED] wrote: Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Type Two | True Having this table I want a report grouping Types and giving me more statistics such as: Type|Active Count| Inactive Count|Active Percent How do i do that? I can think of : select Type from table_name group by Type But that doesn't give me how many active and inactive each had! Please help me here understand how to approach this. Thank you, Mike ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] select all matches for a regular expression ?
I'm going to disagree and say it can be done (maybe).
Use regexp_replace() to convert non-numeric characters. Depending on
your final needs, you could leave it as a comma-separated list or
split it to an array.
select string_to_array(regexp_replace(regexp_replace('hello4 is 4 very
n1ce num8er', '[^0-9]+', ',', 'g'), '^,|,$', '', 'g'),',');
{4,4,1,8}
On Feb 23, 10:18 am, [EMAIL PROTECTED] (Anton Melser) wrote:
On 23/02/07, Tom Lane [EMAIL PROTECTED] wrote:
Anton Melser [EMAIL PROTECTED] writes:
I need to be able to get all the matches for a particular regexp from
a text field that I need to use in another query in a function. Is
this possible with plpgsql? Do I have to install the perl language?
You need plperl (or pltcl; likely plpython would work too) --- the
built-in regex functions don't have any way to return more than the
first match. There's a patch pending to provide more functionality
here for 8.3, but it won't help you today.
Thanks for the info
Cheers
Anton
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] SQL Question - Using Group By
Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Type Two | True Having this table I want a report grouping Types and giving me more statistics such as: Type|Active Count| Inactive Count|Active Percent How do i do that? I can think of : select Type from table_name group by Type But that doesn't give me how many active and inactive each had! Please help me here understand how to approach this. Thank you, Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] complex referential integrity constraints
I don't understand what a weighted constraint would mean. Either the attacker_id can be a wolf, or it can't. Knowing that it is only 1% likely over the long haul is insufficient to disallow any particular transaction. It's certainly true that the constraint as stated is insufficient to guarantee that the table will contain good data. For example if we looked at the maulings table and wolves were always mauling other wolves but never sheep, we would naturally want to dig into that a little more and find out why they weren't picking easier targets. But this is neither here nor there, because NO constraint (foreign key, check, or what have you) is ever strong enough to ensure that the data in a table is completely clean. At least as I understand it, the purpose of these constraints is to allow us to write application code which relies on certain basic invariants being true, i.e. so that we can join animal to animal_type and not have to worry about rows dropping out because some animals had an invalid type, or rows getting added because there are two animal_type records with the same id. Besides, the problem as stated is a proxy for some real problem which is part of a non-zoological project the details of which (a) would take too long to explain and (b) should probably not be posted to a public mailing list. :-) So far, the best ideas I've seen have been: (a) Tom Lane's idea of denormalizing by copying the animal type column into the maulings table with ON UPDATE CASCADE, and then adding a CHECK constraint on that column, and (b) Creating a separate table called wolf and some triggers that ensure that the wolf table will always contain the subset of IDs from the animal table where the type_id is that of a wolf, with a foreign key constraint from that id column back to animal with on delete cascade. This ensures that nobody can delete a wolf or change it into a sheep if it has maulings, but permits it otherwise. For what it's worth, I've adopted the latter solution for the present. Unfortunately, it's too much work to do it everywhere it would be nice to have, so I'm just doing it in some really critical cases and hoping that the others don't break. Thanks, ...Robert -Original Message- From: Alban Hertroys [mailto:[EMAIL PROTECTED] Sent: Friday, February 23, 2007 4:02 AM To: Robert Haas Cc: David Fetter; pgsql-general@postgresql.org Subject: Re: [GENERAL] complex referential integrity constraints Robert Haas wrote: The idea here is that a wolf can attack a sheep, or a wolf can attack another wolf, but sheep can't attack anything. I suppose I could list each wolf in both the predator and prey tables, but that seems a bit duplicative (and causes other problems). ...Robert I'm quite certain a wolf is much more likely to attack a sheep than to attack another wolf, and even more unlikely to attack for example a lion. It seems to me that just the fact that it can isn't enough information. It looks like you need weighted constraints; there's 0 chance that a sheep attacks a wolf, but there's 0 chance that a wolf attacks a sheep, 0 chance it attacks a wolf and 0 chance it attacks a lion. The exact numbers will vary, and I have absolutely no idea what they would be like. It probably requires some kind of ranking system that adjusts according to the known animals and their likelihood to attack eachother. I'm pretty sure you can't get this done without defining some triggers. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Priorities for users or queries?
My problem with [1] is that even for 10 users the percentage of time spent in locks is very high. Can priorities scale? Benjamin Ron Mayer wrote: Bruce Momjian wrote: Hard to argue with that. Is it a strong enough argument to add a TODO? I'm thinking some sort of TODO might be called for. Perhaps two TODOs? * Use the OS's priority features to prioritize backends (and document that it might work better with OS's that support priority inheritance). * Investigate if postgresql could develop an additional priority mechanism instead of using the OS's. Ron Mayer wrote: Magnus Hagander wrote: ... quite likely to suffer from priority inversion ... CMU paper... tested PostgreSQL (and DB2) on TPC-C and TPC-W ...found that...I/O scheduling through CPU priorities is a big win for postgresql. http://www.cs.cmu.edu/~bianca/icde04.pdf Setting priorities seems a rather common request, supposedly coming up every couple months [5]. The paper referenced [1] suggests that even with naive schedulers, use of CPU priorities is very effective for CPU and I/O intensive PostgreSQL workloads. If someone eventually finds a workload that does suffer worse performance due to priority inversion, (a) they could switch to an OS and scheduler that supports priority inheritance; (b) it'd be an interesting case for a paper rebutting the CMU one; and (c) they don't have to use priorities. If a user does find he wants priority inheritance it seems Linux[1], BSD[2], some flavors of Windows[3], and Solaris[4] all seem to be options; even though I've only seen PostgreSQL specifically tested for priority inversion problems with Linux (which did not find problems but found additional benefit of using priority inheritance). [1] Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf [2] BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html [3] Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx [4] Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/ [5] Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] schema design question
On Sat, 24 Feb 2007 00:59:02 -0800 [EMAIL PROTECTED] (snacktime) wrote: Say you have 8 different data models that are related enough to share roughly 70% of the same fields, but the shared fields are not always the same. And also within any given model, some fields can be empty. The business logic is that data is pulled from all the data models and put into a common format that has all the combined fields, and sent over the wire as a transaction. Would most of you create a separate tabel for each transaction type? I would personnally have one single table which gives more opportunity to add some processing types in the future rather than to have to revisit all scripts, routines etc to add a new table. you must however be careful about possible contention on your table: PK should be an integer and table should be clustered: you always append at the end of the table and you can select from that table for past transactions. I'm also curious how others would handle the batch closings. In the past I have created separate tables for open transactions and transactions that have been captured/settled. When a transaction is captured it's moved to a different table instead of just having a column to mark it as captured. Normally I would select all the transactions to capture, insert them into the captured table, delete them from the open transactions table, process the batch, and if the batch goes through commit everything. That narrows down the number of things that can go wrong after you have submitted the batch. The alternative would be to just have a column to mark transactions as capture and leave them all in one table. I've always been paranoid about doing that because it leaves open the possibility of capturing thousands of transactions twice if you have a bug, as opposed to a few hundred at most. I would use a marker field. Moviong all these transactions around seems like an awful lot of data to move. most certainly if you have a rollback to do. IMHO the more data you move around the more fragmented your DB becomes and the more you have to look after it. (Note I am not talking about PostGresSQL, I am too much of a beginner for that, but that's my experience with Sybase, Firebird and Oracle) You probably have several safeguards: your transactions must share a common field like a batch number you could have a serate table holding a status for each batch ( processing, processed, rejected etc...), significant totals of the batch, totals processed, a timestamp for each stage etc This also gives the opportunity to put in place a simple monitoring system (always nice to give your users an insight about what happens in the system and when) My personnal rule of thumb is that even if disk space is cheap, bandwidth and I/O are still at a premium. All my collegues not really playing by this rule have always produced systems which end up running like dead cows over time. The you can create a sweeping mechanism that offloads once in a while processed data to an historical table used for MI, reporting etc... it all depends on what you intend to do with the data. I spent quite a few years working at payment gateways and am now creating an open source platform that does the same thing that your normal payment gateway does. It's been a while since I've had the chance to look at this problem in a fresh light. Most of the processing code at payment gateways is left pretty much untouched once it's working, it's not something you go in and refactor every few months even if it's not perfect. Very interesting project :) Would appreciate any feedback. Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SQL Question - Using Group By
Thank you! Exactly what I needed. Mike On Feb 23, 4:42 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You could use COUNT() in conjunction with NULLIF: select Type, count(nullif(Active, false)) as Active Count, count(nullif(Active, true)) as Inactive Count, 100 * count(nullif(Active, false)) / count(*) as Active Percent from table_name group by Type On Feb 23, 2:50 pm, Mike [EMAIL PROTECTED] wrote: Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Type Two | True Having this table I want a report grouping Types and giving me more statistics such as: Type|Active Count| Inactive Count|Active Percent How do i do that? I can think of : select Type from table_name group by Type But that doesn't give me how many active and inactive each had! Please help me here understand how to approach this. Thank you, Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what happens when you do an unlink() and then the transaction fails for some reason - there's no way to 'rollback' the filesystem operation. I've seen this solution (storing images in filesystem) mostly in MySQL applications, but that's because of (a) lack of transactions in MySQL and (b) somehow sub-optimal handling of binary data as MySQL loads all the data even if it's not needed (this was true for MySQL 3.23 - I'm not sure about the current releases). Anyway, I do recommend storing images in the database, using a 'bytea' column for the binary data (and load them only if reallly needed, using proper projection). You can do some benchmarks, but I've never head performance problems with it on PostgreSQL and the ability to use transactions was invaluable (it saved us hundreds of hours when the machine went down for some reason). Tomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL Question - Using Group By
Hi, I have a question about using Group By. On a table like this: Type (varchar) | Active (boolean) Type One | False Type Two | True Type One | True Type Fifty | Flase Type Two | True Having this table I want a report grouping Types and giving me more statistics such as: Type|Active Count| Inactive Count|Active Percent How do i do that? I can think of : select Type from table_name group by Type This should been quite easy - the trick is aggregate functions omit NULL values (maybe there is some other / better way): SELECT type, COUNT(CASE WHEN active THEN 1 ELSE NULL END) AS active_count, COUNT(CASE WHEN active THEN NULL ELSE 1 END) AS inactive_count, COUNT(CASE WHEN active THEN 1 ELSE NULL END) / COUNT(*) AS active_pct FROM table_name; but have not tested it ;( Tomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
On 2/26/07, Joris Dobbelsteen [EMAIL PROTECTED] wrote: See the discussion [GENERAL] Database versus filesystem for storing images earlier on the List. And man, do I wish people used threading-capable mailers and didn't do tofu-posts. :/ - Joris Cheers, Andrej ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Tomas Vondra [EMAIL PROTECTED] schrieb: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what There are pros and cons, i know. If someone need transactional control on pictures, then store them in the database, right. It's also simpler to do a complete backup, i know. On the other side, if you have only a desktop-computer and you will be able to look at the pictures with other programms (for instance), it's simpler if you have the pictures in the filesystem. And, large binarys in the database is more overhead for the database, the database itself stores the binarys also in the filesystem and every read involves the filesystem and the database. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
This is what i did (to stored pictures in DB)... but i use the following process : 1.store picture on my localhost db 2. export as SQL statement all pictures from my table :-( === it was 7.4Mb 3. import to the remote db hosted by a company. is there an easy way to store image into a hosted DB ? because i can not use any tool to directly insert into DB the pictures :-( they only accept pgadmin interface... thanks a lot, Al. On 2/25/07, Tomas Vondra [EMAIL PROTECTED] wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what happens when you do an unlink() and then the transaction fails for some reason - there's no way to 'rollback' the filesystem operation. I've seen this solution (storing images in filesystem) mostly in MySQL applications, but that's because of (a) lack of transactions in MySQL and (b) somehow sub-optimal handling of binary data as MySQL loads all the data even if it's not needed (this was true for MySQL 3.23 - I'm not sure about the current releases). Anyway, I do recommend storing images in the database, using a 'bytea' column for the binary data (and load them only if reallly needed, using proper projection). You can do some benchmarks, but I've never head performance problems with it on PostgreSQL and the ability to use transactions was invaluable (it saved us hundreds of hours when the machine went down for some reason). Tomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Hello Tomas, Tomas Vondra wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Anyway, I do recommend storing images in the database, using a 'bytea' column for the binary data (and load them only if reallly needed, using proper projection). You can do some benchmarks, but I've never head Would you say the same if the images were each 30MB+ and there were thousands of them, possibly needing to be stored on several hardwares? Thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] perfromance world records
2007/2/24, Joshua D. Drake [EMAIL PROTECTED]: Ron Johnson wrote: On 02/24/07 11:00, Tom Lane wrote: Tomi N/A [EMAIL PROTECTED] writes: ...which made me think: postgresql aims at the same (or very similar) clients and use cases as Oracle, DB2 and MSSQL. I pose the question from an advocacy standpoint: why doesn't postgresql hold a world record of some sort (except performance/price)? Certified TPC tests are *expensive* to run. If you search the PG archives for TPC you will probably find some relevant prior discussions. What about non-certified tests? Or has the TPC copyrighted/licensed/whatever the tests, so that you can only publish certified results? You can not publish TPC tests without a TPC fee :). However there are plenty of other tests such as dbt2 and odbcbench that can give you comparable and free results. I mentioned a TPC test as an example: any kind of (well known) standard test would do. I guess it goes without saying anyone running such a test would do well to send word to the mailing list with a URL to the results. :) t.n.a. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Tomas Vondra wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what happens when you do an unlink() and then the transaction fails for some reason - there's no way to 'rollback' the filesystem operation. I've seen this solution (storing images in filesystem) mostly in MySQL applications, but that's because of (a) lack of transactions in MySQL and (b) somehow sub-optimal handling of binary data as MySQL loads all the data even if it's not needed (this was true for MySQL 3.23 - I'm not sure about the current releases). Dumb question: the picture data is written twice, right? Once in the WAL and once into the table? So the argument can be reduced to: 1) Load into tables for transactional support, con is the write-time hit 2) Load into filesystem for faster load, but you have to provide integrity by another route -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] perfromance world records
Tomi N/A [EMAIL PROTECTED] writes: 2007/2/24, Joshua D. Drake [EMAIL PROTECTED]: You can not publish TPC tests without a TPC fee :). However there are plenty of other tests such as dbt2 and odbcbench that can give you comparable and free results. I mentioned a TPC test as an example: any kind of (well known) standard test would do. If the objective is to claim a world record, we'd look pretty silly trying to do so with a nonstandard, non-certified test. The point of certification in this context is that you have someone else attesting to the validity of your results. Without that, your claim isn't going to be believed. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
On Feb 25, 2007, at 9:26 AM, Tomas Vondra wrote: Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) Andreas Don't do that - the filesystems are not transactional (at least not the usual ones), so you'll lose the ability to use transactions. Imagine what happens when you do an unlink() and then the transaction fails for some reason - there's no way to 'rollback' the filesystem operation. I've seen this solution (storing images in filesystem) mostly in MySQL applications, but that's because of (a) lack of transactions in MySQL and (b) somehow sub-optimal handling of binary data as MySQL loads all the data even if it's not needed (this was true for MySQL 3.23 - I'm not sure about the current releases). You just need to implement it correctly. I've done this by using an in-database delete queue that's polled by an external process to delete the image files. For external image files you don't need to be perfectly transactional, as long as the failure mode is occasionally leaving a file in place when it shouldn't be, as all that does is leak a little filesystem space which can easily be recovered by a periodic task. Anyway, I do recommend storing images in the database, using a 'bytea' column for the binary data (and load them only if reallly needed, using proper projection). You can do some benchmarks, but I've never head performance problems with it on PostgreSQL and the ability to use transactions was invaluable (it saved us hundreds of hours when the machine went down for some reason). The overhead of serving images from postgresql is much higher than serving them directly from the filesystem (as is the overhead of backing the data up). For simple, static images the advantages of full transaction support on the data as well as the metadata are likely to be fairly minimal. Both approaches work, which is going to be better will depend on the details of what you're doing with the images and how far you want it to scale. (But I can't think of any case where *my* preferred approach would be to keep them in the DB). Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why can't I put a BEFORE EACH ROW trigger on a view?
On 02/24/2007 11:24:40 PM, Jaime Casanova wrote: On 2/24/07, Karl O. Pinc [EMAIL PROTECTED] wrote: http://www.postgresql.org/docs/current/static/rules-views.html Actually, i found it very clear: if you create a SELECT rule on a table it becomes a view, this is what postgres does every time you create a view It does say that. But it does not say that if you do it exactly the same thing will happen. I figured postgres does that internally, when it knows it wants to make a view, but if I did it explicitly I'd have a table with a select rule on it that would operate just like a view but would still be a table. And why not let me have a table with a select rule on it that makes the table act like a view, but that I can otherwise fiddle with myself, like adding triggers, and make of it what I want? Then it's up to me to expose the foreign keys or whatever else the triggers need to work. That would be the painless solution that would work just fine for my purposes, however nice it'd be to be able to put BEFORE triggers on VIEWS -- after exposing all columns of the underlying tables etc. (As I fantasized about in a previous post.) why not simply create BEFORE TRIGGERS on the base table, CREATE VIEW on top and use an INSERT/UPDATE/DELETE rule on the view to rewrite those operations to the equivalents on the tables... that way you will have your TRIGGERS validating the data... Because the view has columns that the underlying table does not, that are computed but that I might want to update through, validate, etc. See my previous post. Somebody already asked this exact question. I could write triggers on all my underlying tables that do the instantiated view thing and update another table with all the right info. And _then_ do what you suggest, being sure to pass the data back to the true underlying tables. (Actually, at that point there'd be no point in CREATE VIEW at all.) But that's a lot of work and why go to the trouble when an ordinary view will do just fine (as far as output goes anyway, modification is where I have trouble.) Having a table with real data in it seems like a lot of overhead I don't need. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] perfromance world records
2007/2/25, Tom Lane [EMAIL PROTECTED]: If the objective is to claim a world record, we'd look pretty silly trying to do so with a nonstandard, non-certified test. The point of certification in this context is that you have someone else attesting to the validity of your results. Without that, your claim isn't going to be believed. Makes sense. I got carried away a bit. I guess I'll have to stick to the available case studies...maybe even contribute one or two of those myself. Cheers, t.n.a. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? thanks a lot -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5 I do with ByteA datatype leonel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] General Ledger db design
Martin Winsler wrote: I hope this isn't too far off topic. I've noticed some discussion about referential integrity, the use of nulls, and database design recently here. This is a real world situation where referential integrity needs to be broken in theory, I believe. Does anybody have any experience or knowledge of building financial accounting databases? Am I wrong about this? The problem is that with double entry accounting you have records in tables that both reference other records in the same table as well as different records in other tables depending on some fairly complex logic. For instance an invoice is a financial instrument, so the parent record would naturally want to be part of a company wide journal or ledger. However, its child records would be actual invoice lines as well as two different sets of entries in the general ledger detail, all 3 sets of records must agree with each other on the invoice parent record total. I can only tell you what I've done in the past, you can take it from there. First, there are two very basic tables, the chart of accounts and the list of transactions. Every line in the transaction table links to one account. So far so good. Now we have AR invoices, and AP vouchers. Let's oversimplify for argument and say that when you post an AR invoice you post two entries in the transactions table, a debit to AR and a credit to sales (forget about tax and stuff for now). Likewise for an AP voucher, debit some expense account and credit AP. So now the problem is we've got at least two more potential parent tables for the transaction, being invoices and vouchers, and it gets worse when we add checks received, checks paid, overpayments, allowances and so on and so on. The solution I've always used is to introduce a table of batches. This is the table that unifies all of the others. When you post an invoice, you generate a new batch, give it type AR. The invoice is stamped with the batch #, as are the GL transaction rows. When you post an AP voucher, do the same thing. Same for checks received, checks paid, etc, all of them have different batch types. In short, the problem of too many parents is inverted to produce many children instead, and the problem goes away. -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pulling hair out trying to force replan
I've got some pretty big tables with partial indexes on very specific values. It seems as though no matter what I try to force a replan it won't plan to use the partial indexes because it seems to be caching a plan valid for all potential parameters. I'm using hibernate which uses prepared statements over jdbc. I've tried setting prepareThreshold=0 to no avail. PARTIAL INDEX ON varchar X with varchar_pattern_ops where X like '12345%' LOG: duration: 9640.964 ms execute S_127/C_128: select ... from table this_ ... where this_.TIME$1 and (11 or ((11 or this_.X like $2))) DETAIL: parameters: $1 = '2007-02-02 04:56:38', $2 = '12345%' If i take the query above and substitute manually the constants and do an explain it uses the partial indexes fine, and the query runs less than 10 ms... Any suggestions would be most appreciated, I've been trying to solve this for a week now :( Thanks, Gene
Re: [GENERAL] pulling hair out trying to force replan
I've got some pretty big tables with partial indexes on very specific values. It seems as though no matter what I try to force a replan it won't plan to use the partial indexes because it seems to be caching a plan valid for all potential parameters. I'm using hibernate which uses prepared statements over jdbc. I've tried setting prepareThreshold=0 to no avail. Any suggestions would be most appreciated, I've been trying to solve this for a week now :( Not sure how much this will help you, but you can query The pg_prepared_statements view to find the prepared statement that's causing your headaches (S_127/C_128 in your example) and feed it to DEALLOCATE. http://www.postgresql.org/docs/8.2/interactive/view-pg-prepared-statemen ts.html http://www.postgresql.org/docs/8.2/interactive/sql-deallocate.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] General Ledger db design
On 02/25/2007 06:21:45 PM, Kenneth Downs wrote: Martin Winsler wrote: This is a real world situation where referential integrity needs to be broken in theory, I believe. Does anybody have any experience or knowledge of building financial accounting databases? Am I wrong about this? The problem is that with double entry accounting you have records in tables that both reference other records in the same table as well as different records in other tables depending on some fairly complex logic. For instance an invoice is a financial instrument, so the parent record would naturally want to be part of a company wide journal or ledger. However, its child records would be actual invoice lines as well as two different sets of entries in the general ledger detail, all 3 sets of records must agree with each other on the invoice parent record total. The solution I've always used is to introduce a table of batches. This is the table that unifies all of the others. When you post an invoice, you generate a new batch, give it type AR. The invoice is stamped with the batch #, as are the GL transaction rows. When you post an AP voucher, do the same thing. Same for checks received, checks paid, etc, all of them have different batch types. It's been a while since I've done finance apps but this is my recollection of the situation. The above proposal takes care of the data structure/referential integrity issues, but does not solve the data integrity issues. The only way, at present, to solve the data integrity issues is to write a FOR EACH STATEMENT trigger to be sure that all the rows agree with each other and everything balances. But this can only be done after all the data goes into the database. For instance, insert the credit and debit rows into a temporary table, then insert from the temporary table into the actual GL transaction table in one go, and have a AFTER ... FOR EACH STATEMENT go through and make sure the entire ledger is still in balance. From a performance standpoint this bites. Of course you can insert the financial transaction rows before inserting a row in the table of batches, or whatever the parent table is. Then write triggers on the batch table to make sure everything stays in balance. Your business rules are enforced, for every batch that exists, but your referential integrity is lost and you can wind up with dangling child rows. All the same I sorta prefer this solution because it seems to me that the mess is easier to clean up. The traditional solution has always been to make sure all your applications have no bugs. They need to do everything in transactions and always insert both the credit and debit sides of every financial transaction, otherwise the ledger (or whatever) can get out of balance because one side or another of the financial transaction is missing. (I don't know why the traditional solution is so popular. Maybe because it was always done this way before ACID compliant databases. Or, could be because it puts the onus for cleaning up the mess on the accountants, and they're used to it because it's the same sort of mess they've always had to clean up. Or it could be because application programmers hate it when the db gives them errors and figure they do a good enough job that it's not a problem. I've also heard people complain about triggers because they don't manage their code base and don't know what triggers exist after a while.) You pretty much have the choice of either enforcing business rules or enforcing referential integrity, but not both. At least that was the conclusion I recalling coming to back when I was doing finance stuff. FWIW, I have long lusted after a per-row trigger that would fire on transaction commit to solve these problems. (Or any sort of trigger with access to the row data so that it can be checked.) I couldn't say whether such triggers are technically feasible, but I'm pretty sure nobody's interested enough to do the implementation. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
