Re: [GENERAL] SLEEP in posgresql
Jasbinder Singh Bali napsal(a): Instead of that, I re-engineered my while loop in the stored procedure as follows. ... I was wondering how to implement the SLEEP functionality here. Hello. I can't comment the function itself, but I want to bring something else to your attention. Note, that the stored procedure is always run as a single transaction and by doing the sleep in it, it will probbly run for a long time, or maybe even forever. The problem is that Long running transactions are evil(tm) Postgres, and almost any real database engine for that matter, has problems when there are tansactions that run for a very long time. It prevents the cleanup of stale records, because the engine has to keep them around for this long running transaction. You might consider doing the actual work in the transaction, but the sleeping in between shoud be done outside. Note to PG developers: Is there any thought being given to have the PL/pgSQL scripting language outside the function body? Like Ora has? It would be perfect for this case and I remember more than a dozen times in last year when I could have used it and saved some PHP work (and network communiaction). -- Michal Táborský chief systems architect Internet Mall, a.s. http://www.MALL.cz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Storing files in postgres db
Hello Tam Wei, tam wei wrote: I am intending to store the files inside the postgres DB using the type text (all the files will be pre-encode into base64. The reason for not using the type bytea as I encountered some undesired format(the original file alignment can't be preserved) while extracting the content and display iit using php). You must be doing something wrong on PHP side. Make sure you use pg_escape_bytea/pg_unescape_bytea when storing/loading. We use it and it works with binary files, no problem. You are wasting space and slowing things down with base64. Will it be a bad idea for storing the files in DB? the file size is about 20 ~ 40 KB. 3K files need to be stored per day. Is there any impact on the DB performance? We use it to store files of 50MB without any difficulty. It all depends, of course, on your hardware and load. -- Michal Táborský chief systems architect Internet Mall, a.s. http://www.MALL.cz ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] LOCK ... NOWAIT throws error
Hello. We started using, in one of our applications, the LOCK ... NOWAIT functionality. It works as it's supposed to, but there is one, albeit tiny, annoyance about it. When the LOCK cannot be immediately obtained, this statement logs an ERROR message. But in this case, I think it is not appropriate to call this event an error, since I am well aware it might happen and actually expect it, since I use the NOWAIT switch. Why is it an annoyance? We monitor the logs for ERROR messages and act upon them, but in this case we have to filter it out or ignore it. I wonder, if there isn't a possibility to change this reporting behavior (well, probably no, but I am asking just in case). Or if no, you might take it as a suggestion for next release. Bye. -- Michal Táborský chief systems architect Internet Mall, a.s. http://www.MALL.cz ---(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
