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
[GENERAL] skip duplicate key error during inserts
Hello,I have a table like this:create table dummy (value integer primary key);and I insert a row like thisinsert into dummy values(0);then I want to insert three rows:insert into dummy values(0);insert into dummy values(1);insert into dummy values(2);none of them will be inserted because the first insert is a primary key violation. How can I have postgreSQL not mind about the error and proceed to the next insert. I could send the inserts one at a time but bundling them speeds up the process.Thanks
Re: [GENERAL] looping through query to update column
Thanks for the "ctid" trick. The code below worked fine for rec in select * from fromemail_trades loop update fromemail_trades set recordid = row where ctid = rec.ctid; row := row -1; end loop;The first line is a little different from your's: FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOPHow important is it to specify ctid in the select and to add 'for update'?Thanks againJCR- Original Message From: Albe Laurenz [EMAIL PROTECTED]To: pgsql-general@postgresql.orgSent: Friday, October 13, 2006 6:24:16 AMSubject: Re: [GENERAL] looping through query to update columnRafal Pietrak wrote: You might use 'ctid' to identify the row if you have no suitable How should I use 'ctid'? Like in the case, when I've selected something by means of SELECT ... FOR UPDATE?You lock the table (with LOCK) or the row you're working on(with SELECT FOR UPDATE) so that nobody else can change it whileyou are working on it.You need something like ctid if your table has the fundamental flawof lacking a primary key.Sample:FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOP UPDATE table SET column=value WHERE ctid=row.ctid; ...END LOOP;If your table has a primary key, use that instead and pleaseforget about the ctid.Yours,Laurenz Albe---(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
[GENERAL] looping through query to update column
Hello,I am trying to loop through a table to update one columncreate or replace function foo() returns integer as $$declare rec RECORD; row integer := 0;begin for rec in select * from table loop update rec set recordid = row; row++; end loop; return 0;end;$$ language plpgsqlIn pgadmin, I am getting the following error message, but that does not help me much:ERROR: syntax error at or near "$1" at character 9QUERY: update $1 set recordid = $2 CONTEXT: SQL statement in PL/PgSQL function "foo" near line 6Thanks for any helpJCR
Re: [GENERAL] Storing images in PostgreSQL databases (again)
Why would I set a bytea column (containing picures) as a primary key? Because I want to be sure that the same image is inserted only once (that requirement comes from a real project) and using a primary key for that purpose makes sense to me. Am I going to retrieve an image row by its image data? I would certainly like! For instance, I would like to get the pictures whose main color is green (requirement from a real project), and a select * from images where main_color(image) = 'green' would be nice.JCR- Original Message From: Alexander Staubo [EMAIL PROTECTED]To: Jean-Christophe Roux [EMAIL PROTECTED]Cc: pgsql-general@postgresql.orgSent: Thursday, October 5, 2006 7:35:04 PMSubject: Re: [GENERAL] Storing images in PostgreSQL databases (again)On Oct 6, 2006, at 01:29 , Jean-Christophe Roux wrote: By the way, is it practical to set a bytea column (containing pictures) as primary key? That would severely slow down many operations I guess.Why would you? It's possible, but completely impractical, since imagedata typically exceeds the index page size. Moreover, are you reallygoing to retrieve an image row by its image data?Alexander.---(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] Storing images in PostgreSQL databases (again)
Hi,If the database had built-in functions to manipulate images (make a thumbnail, add text ont it.., make a montage of two pictures) and I could write something like select thumbnail(image_field, 100, 100) from images_tablethat would be a good reason to go the db route versus the filesystem route. A database does more then storing data, it makes convenient to play with them. Once my pictures are stored in the database, how do I make thumbnails for instance? Maybe the solution already exists; I am curious here. Is there a way to integrate ImageMagick into a PostgreSQL workflow?By the way, is it practical to set a bytea column (containing pictures) as primary key? That would severely slow down many operations I guess. JCR- Original Message From: Alexander Staubo [EMAIL PROTECTED]To: pgsql-general@postgresql.orgCc: DEV [EMAIL PROTECTED]Sent: Thursday, October 5, 2006 6:30:07 PMSubject: Re: [GENERAL] Storing images in PostgreSQL databases (again)On Oct 5, 2006, at 19:47 , DEV wrote: I have seen several posts pertaining to the "overhead" difference in storing in a db table versus the file system.What is this difference?Well, there's not much space overhead to speak of. I tested with abunch of JPEG files:$ find files | wc -l 2724$ du -hs files213MfilesWith an empty database and the following schema: create table files (id serial, data bytea); alter table files alter column data set storage external;When loaded into the database:$ du -hs /opt/local/var/db/postgresql/base/16386223M/opt/local/var/db/postgresql/base/16386On my MacIntel with PostgreSQL from DarwinPorts -- a configuration/ port where PostgreSQL performance does *not* shine, incidentally --PostgreSQL can insert the image data at a pretty stable 2.5MB/s. It'sstill around 30 times slower than the file system at reading thedata. (I would love to run a benchmark to provide detailed timings,but that would tie up my laptop for too long.)Alexander.---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] now() and time zone
Hello,I am a bit sorry to come back on that topic but I just cannot get it right. How comes that select now() at time zone 'EST'returns "2006-10-04 15:59:26.713623"when it is actually 16:59 on the east coast? Can it be that the server where the PostgreSQL database is located is not properly configured?Many thanksJean
Re: [GENERAL] now() and time zone
Yes you are right, that's what I have just realized; I should be using EDT instead of EST. Sorry for the botherJCR- Original Message From: Martijn van Oosterhout kleptog@svana.orgTo: Jean-Christophe Roux [EMAIL PROTECTED]Cc: pgsql-general@postgresql.orgSent: Wednesday, October 4, 2006 5:06:42 PMSubject: Re: [GENERAL] now() and time zoneOn Wed, Oct 04, 2006 at 02:01:18PM -0700, Jean-Christophe Roux wrote: Hello, I am a bit sorry to come back on that topic but I just cannot get it right. How comes that select now() at time zone 'EST' returns "2006-10-04 15:59:26.713623" when it is actually 16:59 on the east coast? Can it be that the server where the PostgreSQL database is located is not properly configured?Maybe there's summar time/daylight savings time? I'm unsure if "EST" issupposed to reflect that.You can say things like:select now() at time zone 'America/California';Perhaps that gets you something more reasonable?Have a nice day,-- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate.
Re: [GENERAL] pg web hosting with tsearch2?
I don't know if A2webhosting.com specifically supports tsearch2, but they say they offer 8.1 with procedural language installed.JCR- Original Message From: Joshua D. Drake [EMAIL PROTECTED]To: Rick Schumeyer [EMAIL PROTECTED]Cc: pgsql-general@postgresql.orgSent: Friday, September 29, 2006 5:04:51 PMSubject: Re: [GENERAL] pg web hosting with tsearch2?Rick Schumeyer wrote: I hope pg-general is the correct forum for this question.if not please let me know the correct location. I have a pg application that uses tsearch2.I would like to move this application off my local machine and onto a web host somewhere.I have some questions regarding this: 1) What is the preferred postgresql text searching tool these days? Is it still tsearch2?Yes and pg_trgm (similar but different) 2) Can someone suggest a web host service that includes tsearch2 (or an equivalent text searching component)?www.commandprompt.com 3)All the web hosts I am aware of are still offering only pg 7.4. Does anybody offer pg 8.x ?8.0.x and 8.1.x only.Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. ===Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensivePostgreSQL solutions since 1997 http://www.commandprompt.com/---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ...unknown user name or bad password error during install
Hi,I am trying to install PostgreSQL 8.1.3 on windows xp. This computer had previous versions of postgresql installed but they have been uninstalled.When the installer reach the window Service Configuration, I keep the default values and add a password. I am getting the error:"Invalid username specified: Logon failure: unknown user name or bad password"My understanding is that it is not normal.I searched a little and found BUG #1873 by Lee Benson with an error quite similar to the one I am facing. But that does not help me out much..Any idea on what I should do?Thanks a lot in advanceJCR Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: [GENERAL] Sequence skipping values
Micheal,Thanks a lot for you very clear explanation. To solve the problem, I have created another table so that the table does not use a rule to update itself. It works fine now.RegardsJCRMichael Fuhr [EMAIL PROTECTED] wrote: On Sat, Feb 11, 2006 at 06:06:21AM -0800, Jean-Christophe Roux wrote: Yes you are right, I did not show one rule on table topics: CREATE OR REPLACE RULE topics_last_administrator_id AS ON INSERT TO topics DO UPDATE topics SET last_administrator_id = new.administrator_id WHERE topics.topic_id = new.topic_id; I am going to try to replicate the problem, using a local 8.1.2 database. No need; the version shouldn't matter in this case. The above ruleis responsible because of what I mentioned in a previous message,viz., new.topic_id is
being
rewritten as an _expression_ instead ofa constant. If the insert doesn't provide a value for topic_idthen it takes its value from its default _expression_, which is acall to nextval. When the rule is rewritten, new.topic_id isn'treplaced with that value but rather with the nextval _expression_.Here's another example:CREATE TABLE foo (id serial PRIMARY KEY, x integer, lastx integer);CREATE RULE foo AS ON INSERT TO foo DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = NEW.id;INSERT INTO foo (x) VALUES (1);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 | (1 row)INSERT INTO foo (x) VALUES (2);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 |3 | 2 | (2 rows)INSERT INTO foo (x) VALUES (3);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 |3 | 2 |6 | 3 | (3 rows)As you can s
ee, this
example doesn't do what the rule appears tointend. The last insert, for example, causes the following updatestatement to be run:UPDATE foo SET lastx = 3 WHERE id = nextval('foo_id_seq')Since nextval is volatile each row in the table is checked, causingnextval to be evaluated each time; that's why it's incrementing bythe number of rows in the table. Also notice that lastx isn't beingassigned because id never matches the sequence's next value -- doyou see that problem in your case as well?You might be able to use a rule that uses currval instead of referringto the id column (but see below for a warning):CREATE RULE foo AS ON INSERT TO foo DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = currval('foo_id_seq');If we drop and recreate the foo table and add the above rule thenwe get this:INSERT INTO foo (x) VALUES (1);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 |
1(1
row)INSERT INTO foo (x) VALUES (2);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 | 1 2 | 2 | 2(2 rows)INSERT INTO foo (x) VALUES (3);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3(3 rows)Unfortunately the update will fail to set lastx correctly if youinsert multiple rows with INSERT ... SELECT:INSERT INTO foo (x) SELECT n FROM generate_series(4, 6) AS g(n);SELECT * FROM foo; id | x | lastx +---+--- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 |5 | 5 |6 | 6 | 4(6 rows)I haven't considered other cases you so you might find additionalfailure modes. This method is also inefficient because currval isvolatile so each row in the table will have to be checked, whichwill make the insert/update slow as the table grows. Additionally,the
update creates a dead tuple for every insert so you shouldvacuum the table often if it's updated often.A trigger would probably be better for this. If your databasedoesn't have a trigger-capable language like PL/pgSQL and yourwebhosting admins won't create it for you, then consider changingservices.-- Michael Fuhr
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
Re: [GENERAL] Sequence skipping values
Hi Michael,I'm running PostgreSQL 7.4.5 on a shared wehosting, which means among other things that I have limited control over the database settings and capabilities, For instance, I cannot install languages and therefore I cannot write triggers.Yes you are right, I did not show one rule on table topics:CREATE OR REPLACE RULE topics_last_administrator_id AS ON INSERT TO topics DO UPDATE topics SET last_administrator_id = new.administrator_id WHERE topics.topic_id = new.topic_id;I am going to try to replicate the problem, using a local 8.1.2 database. Thank you very much for your helpJCRMichael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 08:10:36PM -0800, Jean-Christophe Roux wrote: here are the structures of the table involved:I co uldn't duplicate the problem in 8.1.2 or 8.0.6 with the codeyou posted. I created the given tables and rules (plus guesses forthe administrators and status_list tables), then inserted severalrecords into topics, then inserted a few records into releases,then inserted a few more records into topics. The topics_id_seqsequence incremented by one each time with no gaps; that probablymeans my test didn't match exactly what you're doing.Does the topics table have any rules or triggers that you didn'tshow? Could you post a minimal but complete test case, i.e, allSQL statements that somebody could load into an empty database toreproduce the problem? If not then it might be useful to see theEXPLAIN ANALYZE output of a series of statements that exhibit theunexpected behavior. What version of PostgreSQL are you running?-- Michael Fuhr Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
[GENERAL]
Hello,I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on!If someone could give me a clue that wold be greatly appreciated thanksJC What are the most popular cars? Find out at Yahoo! Autos
[GENERAL] Sequence skipping values
Hello,I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on!If someone could give me a clue that wold be greatly appreciated thanksJC Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: [GENERAL] Sequence skipping values
Hi,Thanks four your answers. Let me give more details here.The table with the id not incrementing by 1 as I expected is named topics.I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated.Each of those three tables contain a column that refer to topics.id as a foreign key.Those three columns contain id automatically generated by sequences and I have not observed any problemThanksJCSteve Crawford [EMAIL PROTECTED] wrote: Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JCAny rules, triggers, etc. involved? How are the inserts done (insert one record into the table)? What relationships does the table have to any other tables? Anything else accessing that sequence?There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence.Cheers,Steve Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: [GENERAL] Sequence skipping values
Hi here are the structures of the table involved:CREATE TABLE topics( topic_id int8 NOT NULL DEFAULT nextval('topics_id_seq'::text), datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, topic text NOT NULL, administrator_id int8 NOT NULL, status_id int8 DEFAULT 0, last_change timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, links int8 DEFAULT 0, releases int8 DEFAULT 0, last_administrator_id int8, CONSTRAINT topics_pk PRIMARY KEY (topic_id), CONSTRAINT topics_fk_administrator_id FOREIGN KEY (administrator_id) REFERENCES administrators (administrator_id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT topics_status_fk FOREIGN KEY (status_id) REFERENCES status_list (status_id)
MATCH
SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT) select * from topics_id_seq"topics_id";1224;1;9223372036854775807;0;1;23;f;tit is this topics_id_seq that does not increment by one unit at a time, but rather by the number of rows of the table topics.To make things shorter, I am posting only one of the three tables. The other two tables are similar and have the same kind of rulesCREATE TABLE releases( topic_id int8 NOT NULL, release_id int8 NOT NULL DEFAULT nextval('releases_id_seq'::text), datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, body text NOT NULL, administrator_id int8 NOT NULL, CONSTRAINT releases_pk PRIMARY KEY (release_id), CONSTRAINT releases_fk_administrator_id FOREIGN KEY (administrator_id) REFERENCES administrators (administrator_id) MATCH
SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT releases_fk_topic_id FOREIGN KEY (topic_id) REFERENCES topics (topic_id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT) CREATE OR REPLACE RULE releases_increment_topics AS ON INSERT TO releases DO UPDATE topics SET releases = topics.releases + 1 WHERE topics.topic_id = new.topic_id;CREATE OR REPLACE RULE releases_last_administrator_id AS ON INSERT TO releases DO UPDATE topics SET last_administrator_id = new.administrator_id WHERE topics.topic_id = new.topic_id;CREATE OR REPLACE RULE releases_last_change AS ON INSERT TO releases DO UPDATE topics SET last_change = now() WHERE topics.topic_id = new.topic_id;Thanks again for your time and explanations; it is qu
ite
useful.RegardsJCRMichael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 03:27:23PM -0800, Jean-Christophe Roux wrote: The table with the id not incrementing by 1 as I expected is named topics. I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated. Each of those three tables contain a column that refer to topics.id as a foreign key. Those three columns contain id automatically generated by sequences and I have not observed any problemThe word "rules" attracts attention; questions about sequences beingincremented multiple times due to rules appear in the lists regularly.The problem is that where you might think the rule uses a value it'sreally using an expr
ession,
so each time you use the "value" in therule you're evaluating the _expression_ again. Example:CREATE TABLE foo (id serial);CREATE TABLE bar (id1 integer, id2 integer, id3 integer);CREATE RULE foorule AS ON INSERT TO foo DO ALSO INSERT INTO bar VALUES (NEW.id, NEW.id, NEW.id);INSERT INTO foo DEFAULT VALUES;SELECT * FROM foo; id 1(1 row)SELECT * FROM bar; id1 | id2 | id3 -+-+- 2 | 3 | 4(1 row)When the rule rewrote the query it didn't use INSERT INTO bar VALUES (1, 1, 1)but rather INSERT INTO bar VALUES (nextval('foo_id_seq'), nextval('foo_id_seq'), nextval('foo_id_seq'))because NEW.id evaluates to a nextval _expression_, not to the resultof that _expression_.If you post the table definitions as Steve requested we'll be ableto see whether the above is indeed what's happening.--
Michael
Fuhr
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
