Hi there,
I have a procedure which uses temporary objects (table and sequence). I
tried to optimize it, using common variables (array and long varchar)
instead. I didn't found any difference in performance, but I'd like to
choose the best option from other points of view. One of them is the mem
Harpreet Dhaliwal wrote:
And this feature i.e. transactional DDL is not there in other major
RDBMS like sql server, oracle etc?
thanks
~Harpreet
<...snipped earlier postings...>
It surprised me when I saw Oracle's behavior. Informix supports DDL
within transactions quite happily:
create
On 14.08.2007 23:13, Dmitry Koterov wrote:
Pconnects are absolutely necessary if we use tsearch2, because it
initializes its dictionaries on a first query in a session. It's a very
heavy process (500 ms and more). So, if we do not use pconnect, we waste
about 500 ms on each DB connection. Too muc
Hi,
i would like to check (via PHP or C#) if my database has been correctly
created.
for that i use the following SQL :
select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy';
this i repeat till i check all tables.
But how to check sequences, index, functions, and so on ?
thanks
On Wed, Aug 15, 2007 at 10:08:36AM +0200, Alain Roger wrote:
> i would like to check (via PHP or C#) if my database has been correctly
> created.
> for that i use the following SQL :
> select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy';
> this i repeat till i check all tables.
On 15.08.2007 10:53, Torsten Zühlsdorff wrote:
If the dictionary is not too large, you should store it directly in the
memory of the server. Therefore you can use Shared Memory
(http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php).
Uhm, but how does TSearch get it from there? An
On Wednesday 15 August 2007 1:58:07 am Harpreet Dhaliwal wrote:
> So you mean to say something like this as far as oracle is concerned:
>
> BEGIN
> DDL 1 (commits right after its execution)
> DDL 2 (commits right after its execution)
> END
>
> That means there's no concept of putting DD
On 8/15/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
> And this feature i.e. transactional DDL is not there in other major RDBMS
> like sql server, oracle etc?
The subject of transactional DDL and its prevalence was discussed in a
May thread, "why postgresql over other RDBMS"
(http://archives.
It was SELinux denying apache permission to make TCP connections!
I thought I had SELinux turned off but it wasn't. To be sure it is do
/usr/sbin/sestatus | grep SELinux
and if it comes back with anything other than SELinux status: disabled it's
still running.
While I was talking to the php
Hello,
I am sorry, this mail had to be send only to pgsql-general
nice a day
Pavel Stehule
-- Forwarded message --
From: Pavel Stehule <[EMAIL PROTECTED]>
Date: 15.8.2007 8:01
Subject: is this trigger safe and efective? - locking (caching via triiggers)
To: PostgreSQL Hackers <[E
Hello,
Imagine a web application that process text search queries from
clients. If one types a text search query in a browser it then sends
proper UTF-8 characters and application after all needed processing
(escaping, checks, etc) passes it to database. But if one modifies URL
of the query adding
Hello.
In PL/PGSQL I could write:
BEGIN
SELECT * FROM non_existed_table;
EXCEPTION
WHEN ... THEN ...
END;
How to do it in PL/Perl? I tried the standard for Perl trapping method:
eval {
spi_exec_query("SELECT * FROM non_existed_table");
};
if ($@) { ... }
but it does not work - it says th
On Wed, Aug 15, 2007 at 03:41:30PM +0400, Ivan Zolotukhin wrote:
> Hello,
>
> Imagine a web application that process text search queries from
> clients. If one types a text search query in a browser it then sends
> proper UTF-8 characters and application after all needed processing
> (escaping, ch
On 15/08/07, Ivan Zolotukhin <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Imagine a web application that process text search queries from
> clients. If one types a text search query in a browser it then sends
> proper UTF-8 characters and application after all needed processing
> (escaping, checks, etc)
Hi,
Thanks for the clarification. It helps to resolve the problem. Now, the page
can be fully loaded within 2 seconds.
Thanks.
From: "Scott Marlowe" <[EMAIL PROTECTED]>
To: "carter ck" <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database Select Slow
Date: Fri
I have few queries regarding the use of Stored Procedures, Functions
and Triggers in an RDBMS.
(1) When to use Stored Procedure? Writing an INSERT query in a Stored
Procedure is better or firing it from the application level?
(2) Can a Trigger call a Stored Procedure?
(3) What type of code must
"Tom Lane" <[EMAIL PROTECTED]> writes:
> Ow Mun Heng <[EMAIL PROTECTED]> writes:
>> Ps : Is it this list's norm to have the OP/sender in the "to" list and
>> mailing list on the "CC" list?
>
> Yes. If you don't like that you can try including a "Reply-To: "
> header in what you send to the list;
"Ron Olson" <[EMAIL PROTECTED]> writes:
> Hi all-
>
> I am evaluating databases for use in a large project that will hold image
> data as blobs. I know, everybody says to just store pointers to files on the
> disk...
Well not everyone. I usually do, but if you're not handling these blobs under
he
Rohit wrote:
I have few queries regarding the use of Stored Procedures, Functions
and Triggers in an RDBMS.
These are all easy questions to answer: "it depends".
OK, so you might want some reasons...
(1) When to use Stored Procedure? Writing an INSERT query in a Stored
Procedure is better or
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
>> >SELECT * from trades where id = 9
>> >and c_id =
>> >ORDER by s_id;
>> >
>> >SELECT * from trades where id = 9
>> >and s_id = 0
>> >ORDER by created_on desc ;
>> >
>> >SELECT * from trades where id = 9
Hello,
Well, PostgreSQL is correct entirely, I would post this message to the
-hackers list otherwise :) The question was rather about application
processing of user input not about change of database reaction on
broken UTF-8 string. But I am 100% sure one should fix the input in
this case since w
you can use SET TRANSACTION LEVEL READ UNCOMMITTED to acquire the dirty
reads
From your perspective how *should* the DB handle this?
M
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed.
> you do a lot of queries like that and the id,s_id restriction isn't very
> selective you might look into tsearch2 which can index that type of query.
>
Thanks. Does tsearch2 come installed with 8.2.3? I am not techie
enough to do all the compiling stuff so I'm hoping it does! How can I
check?
On 8/15/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
> > I for one have a reputation of running spam filters that eat pets and small
> > children ... so if you want to be sure to get through to me, don't forget to
> > cc: the list.
>
> They eat all my emails
Hello,
Actually I tried smth like $str = @iconv("UTF-8", "UTF-8//IGNORE",
$str); when preparing string for SQL query and it worked. There's
probably a better way in PHP to achieve this: simply change default
values in php.ini for these parameters:
mbstring.encoding_translation = On
mbstring.subst
I'm grappling with a lot of reporting code for our app that relies on
queries such as:
SELECT COUNT(*) FROM TABLE WHERE (conditions)...
And I still do not find, from the discussions on this thread, any
truly viable solution for this. The one suggestion is to have a
separate counts table,
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> I'm grappling with a lot of reporting code for our app that relies on
> queries such as:
>
> SELECT COUNT(*) FROM TABLE WHERE (conditions)...
>...
> The number of such possibilities for multiple WHERE conditions is
> infinite...
Depends on th
On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> "Phoenix Kiula" <[EMAIL PROTECTED]> writes:
>
> > I'm grappling with a lot of reporting code for our app that relies on
> > queries such as:
> >
> > SELECT COUNT(*) FROM TABLE WHERE (conditions)...
> >...
> > The number of such possib
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> I'm grappling with a lot of reporting code for our app that relies on
> queries such as:
>
> SELECT COUNT(*) FROM TABLE WHERE (conditions)...
>
> And I still do not find, from the discussions on this thread, any
> truly viable solution
Phoenix Kiula wrote:
SELECT COUNT(*) FROM TABLE WHERE (conditions)...
I am not sure what the advice here is. The WHERE condition comes from
the indices. So if the query was not "COUNT(*)" but just a couple of
columns, the query executes in less than a second. Just that COUNT(*)
become
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> > "Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> >
> > > I'm grappling with a lot of reporting code for our app that relies on
> > > queries such as:
> > >
> > > SELECT COUNT(*) FROM T
"Alain Roger" <[EMAIL PROTECTED]> writes:
> i would like to check (via PHP or C#) if my database has been correctly
> created.
> for that i use the following SQL :
> select * from pg_tables where tablename = 'xxx' AND schemaname = 'yyy';
> this i repeat till i check all tables.
> But how to check s
In some examples posted to this forum, it seems to me that when people
execute queries in the psql window, they also see "90 ms taken"
(milliseconds), which denotes the time taken to execute the query.
Where can I set this option because I'm not seeing it in my psql
window on both Win XP and Linux.
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> On 15/08/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> > > On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> > > > "Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> > > >
> > > > > I'm
--- Scott Marlowe <[EMAIL PROTECTED]> wrote:
> Generally, for these kinds of things it's often best to use
> materialized views / rollup tables so that you aren't re-aggregating
> the same data over and over.
I don't know if this was already mentioned, but here is one of the links that
describe t
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> Sorry I was not clear. Imagine an Amazon.com search results page. It
> has about 15 results on Page 1, then it shows "Page 1 of 190".
I don't think that amazon or google really need to give an accurate count in
determining an
estimated number of pag
On 15/08/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> > On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> > > "Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> > >
> > > > I'm grappling with a lot of reporting code for our app that relies o
--- Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> In some examples posted to this forum, it seems to me that when people
> execute queries in the psql window, they also see "90 ms taken"
> (milliseconds), which denotes the time taken to execute the query.
> Where can I set this option because I'm no
> Yes, optimization. :) You don't need an exact count to tell someone
> that there's more data and they can go to it.
In general, I agree. But my example of Amazon was only to illustrate
the point about two queries and why they may be needed. I seem to see
many more pages than you do, but in any
On Wednesday 15. August 2007, Phoenix Kiula wrote:
>In some examples posted to this forum, it seems to me that when people
>execute queries in the psql window, they also see "90 ms taken"
>(milliseconds), which denotes the time taken to execute the query.
>Where can I set this option because I'm no
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> In some examples posted to this forum, it seems to me that when people
> execute queries in the psql window, they also see "90 ms taken"
> (milliseconds), which denotes the time taken to execute the query.
> Where can I set this option because
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> In some examples posted to this forum, it seems to me that when people
> execute queries in the psql window, they also see "90 ms taken"
> (milliseconds), which denotes the time taken to execute the query.
> Where can I set this option because I'm not s
On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:
I write sample about triggers and i have question. is my solution
correct and exists better solution?
Regards
Pavel Stehule
DROP SCHEMA safecache CASCADE;
CREATE SCHEMA safecache;
CREATE TABLE safecache.source_tbl(category int, int_value int)
On Wed, Aug 08, 2007 at 06:35:51PM -0300, Anderson Alves de Albuquerque wrote:
> After user $USER execute this ALTER, it get change PASSWORD. Could I block
> command ALTER password to user $USER?
No, there's no way to do that. You might want to look at using
ident-based authentication for that u
On Thu, Aug 09, 2007 at 09:14:55PM -0400, Steve Madsen wrote:
> On Aug 8, 2007, at 6:08 PM, Decibel! wrote:
> >Something else I like to look at is pg_stat_all_tables seq_scan and
> >seq_tup_read. If seq_scan is a large number and seq_tup_read/
> >seq_scan is
> >also large, that indicates that you
I apologize about the CC; I thought I had done so.
There are fourteen (14) distinct values in rcrd_cd. And I don't know if this
counts as something odd, but I got the following values by doing a vacuum full
analyze, then running the set with index, dropping index, running set without.
> I think you're looking for the \timing command?
> http://www.postgresql.org/docs/8.2/static/app-psql.html
> (under meta-commands, about halfway down the page)
Thanks everyone. "\timing" it is!
Happy camper.
---(end of broadcast)---
TIP 5: don't
On Aug 15, 2007, at 7:41 AM, Ivan Zolotukhin wrote:
What is the best practice to process such a broken strings before
passing them to PostgreSQL? Iconv from utf-8 to utf-8 dropping bad
characters?
This rings of GIGO... if your user enters garbage, how do you know
what they wanted? You don'
Richard Broersma Jr <[EMAIL PROTECTED]> writes:
> However, notice that "\timing" and
> explain analyze do not exactly agree on the results they produce.
\timing reports the total elapsed time as seen at the client. EXPLAIN
ANALYZE tells you about the query execution path inside the server; so
it
On Fri, Aug 10, 2007 at 06:34:03PM +0100, Simon Riggs wrote:
> On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote:
> > I just want to confirm that the cluster/MVCC issues are due to
> > transaction visibility. Assuming that no concurrent access is happening
> > to a given table when the clust
In response to "Phoenix Kiula" <[EMAIL PROTECTED]>:
> > Yes, optimization. :) You don't need an exact count to tell someone
> > that there's more data and they can go to it.
>
>
> In general, I agree. But my example of Amazon was only to illustrate
> the point about two queries and why they may
On Aug 15, 2007, at 9:36 AM, Phoenix Kiula wrote:
I'm grappling with a lot of reporting code for our app that relies on
queries such as:
SELECT COUNT(*) FROM TABLE WHERE (conditions)...
And I still do not find, from the discussions on this thread, any
truly viable solution for this. T
On Fri, Aug 10, 2007 at 09:38:36PM +0400, Dmitry Koterov wrote:
> Hello.
>
> I have a number of deadlock because of the foreign key constraint:
>
> Assume we have 2 tables: A and B. Table A has a field fk referenced to
> B.idas a foreign key constraint.
>
>
> -- transaction #1
> BEGIN;
> ...
>
On Sun, Aug 12, 2007 at 05:40:26PM -0400, Harpreet Dhaliwal wrote:
> Hi,
>
> Lately I completed the business logic of my application and all related
> database work.
>
> Now i need to check the performance of my database, how much load it can
> bear, perfomance to different queries and stored pro
2007/8/15, Erik Jones <[EMAIL PROTECTED]>:
> On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:
>
> > I write sample about triggers and i have question. is my solution
> > correct and exists better solution?
> >
> > Regards
> > Pavel Stehule
> >
> > DROP SCHEMA safecache CASCADE;
> >
> > CREATE SCHE
Andrew Edson wrote:
I apologize about the CC; I thought I had done so.
no problem
There are fourteen (14) distinct values in rcrd_cd. And I don't know
if this counts as something odd, but I got the following values by
doing a vacuum full analyze, then running the set with index,
dropping ind
On Aug 15, 2007, at 11:14 AM, Pavel Stehule wrote:
2007/8/15, Erik Jones <[EMAIL PROTECTED]>:
On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:
I write sample about triggers and i have question. is my solution
correct and exists better solution?
Regards
Pavel Stehule
DROP SCHEMA safecache C
Yes. The only difference between the two selects was that the index on the
table in question was dropped. As far as I know, that was the only partial
index on there, although since it's a test db, I could probably go in and
experiment on a few more if needed.
This problem may have alread
Andrew Edson <[EMAIL PROTECTED]> writes:
> This problem may have already been solved; I'm using an older
> version of Postgres; 8.1.3.
Ah. I think your result is explained by this 8.1.4 bug fix:
2006-05-18 14:57 tgl
* src/backend/optimizer/plan/createplan.c (REL8_1_STABLE): When a
On 15/08/07, Ivan Zolotukhin <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Actually I tried smth like $str = @iconv("UTF-8", "UTF-8//IGNORE",
> $str); when preparing string for SQL query and it worked. There's
> probably a better way in PHP to achieve this: simply change default
> values in php.ini for t
Tom Lane <[EMAIL PROTECTED]> wrote: Ah. I think your result is explained by
this 8.1.4 bug fix:
2006-05-18 14:57 tgl
* src/backend/optimizer/plan/createplan.c (REL8_1_STABLE): When a
bitmap indexscan is using a partial index, it is necessary to
include the partial index predicate in the scan'
Andrew Edson wrote:
Yes. The only difference between the two selects was that the index
on the table in question was dropped. As far as I know, that was the
only partial index on there, although since it's a test db, I could
probably go in and experiment on a few more if needed.
This problem m
Couple of questions with porting:
1. I have been playing around with my databases locally on Win XP so
as not to hurt our website traffic. Now I would like to move the
database to a Linux CentOS server. Can I use pg_dump on Windows and
pg_restore it on Linux? If so, any tips on what I should keep
On Aug 15, 2007, at 11:52 AM, Decibel! wrote:
I can't really think of a case where a seqscan wouldn't return all the
rows in the table... that's what it's meant to do.
Isn't a sequential scan the only option if an appropriate index does
not exist? E.g., for a query with a WHERE clause, but n
Hi all,
I am still, after quite some time, wrangling over the time zone
system in my app. I have sorted out all the internal handling, however I
am still uncertain as to what the best way to get the user to select
their time zone is.
I was thinking of having users just select their time
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> > What, exactly, does that mean?
> >
> > That PostgreSQL should take things in invalid utf-8 format and just store
> > them?
> > Or that PostgreSQL should autoconvert from invalid utf-8 to valid
> > utf-8, guessing the proper codes?
> >
> > Se
On Thu, 16 Aug 2007, Phoenix Kiula wrote:
I am not advocating what others should do. But I know what I need my
DB to do. If I want it to store data that does not match puritanical
standards of textual storage, then it should allow me to...
It does allow that: store it as a BLOB, and then treat
On 8/15/07, Naz Gassiep <[EMAIL PROTECTED]> wrote:
> Hi all,
> I am still, after quite some time, wrangling over the time zone
> system in my app. I have sorted out all the internal handling, however I
> am still uncertain as to what the best way to get the user to select
> their time zone is.
On 16/08/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> > Couple of questions with porting:
> >
> > 1. I have been playing around with my databases locally on Win XP so
> > as not to hurt our website traffic. Now I would like to move the
> > da
On Wednesday 15. August 2007, Phoenix Kiula wrote:
>Couple of questions with porting:
>
>1. I have been playing around with my databases locally on Win XP so
>as not to hurt our website traffic. Now I would like to move the
>database to a Linux CentOS server. Can I use pg_dump on Windows and
>pg_re
- Original Message -
From: "madhtr" <[EMAIL PROTECTED]>
To: "Tom Lane" <[EMAIL PROTECTED]>
Cc:
Sent: Wednesday, February 14, 2007 22:33
Subject: Re: [GENERAL] pqlib in c++: PQconnectStart PQconnectPoll
Another line of thought, given the reading-between-the-lines conclusion
that you a
> What, exactly, does that mean?
>
> That PostgreSQL should take things in invalid utf-8 format and just store
> them?
> Or that PostgreSQL should autoconvert from invalid utf-8 to valid
> utf-8, guessing the proper codes?
>
> Seriously, what do you want pgsql to do with these invalid inputs?
PG
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> On 15/08/07, Ivan Zolotukhin <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > Actually I tried smth like $str = @iconv("UTF-8", "UTF-8//IGNORE",
> > $str); when preparing string for SQL query and it worked. There's
> > probably a better way in PHP
On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> Couple of questions with porting:
>
> 1. I have been playing around with my databases locally on Win XP so
> as not to hurt our website traffic. Now I would like to move the
> database to a Linux CentOS server. Can I use pg_dump on Windows and
On Tue, Aug 14, 2007 at 10:50:33AM +0800, Ow Mun Heng wrote:
> Hi,
>
> Writing a script to pull data from SQL server into a flat-file (or just
> piped in directly to PG using Perl DBI)
>
> Just wondering if the copy command is able to do a replace if there are
> existing data in the Db already. (
> At least on a *nix system, collation is based on the value of the LC_ALL
> environment variable at dbinit time. There's nothing you can do about
> it in a live database. IMO that's a little awkward, and is what finally
> made me change the global from ISO-8859-1 to UTF-8 on my three Gentoo
> Linu
On Thu, 16 Aug 2007, Phoenix Kiula wrote:
1. Even if it were bytea, would it work with regular SQL operators
such as regexp and LIKE?
2. Would tsearch2 work with bytea in the future as long as the stuff
in it was text?
As far as I know, regexp, [i]like, tsearch2, etc. all require valid text
"Phoenix Kiula" <[EMAIL PROTECTED]> writes:
> Thanks. Is there an encoding that is so flexible that it will silently
> accept whatever I send to it without throwing an exception?
SQL_ASCII does that. Whether it's a good idea to use it is
questionable. One thing to think about is that you will be
On Wed, Aug 15, 2007 at 01:26:02PM -0400, Steve Madsen wrote:
> On Aug 15, 2007, at 11:52 AM, Decibel! wrote:
> >I can't really think of a case where a seqscan wouldn't return all the
> >rows in the table... that's what it's meant to do.
>
> Isn't a sequential scan the only option if an appropriat
On 16/08/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote:
> On 16/08/07, Ben <[EMAIL PROTECTED]> wrote:
> > On Thu, 16 Aug 2007, Phoenix Kiula wrote:
> >
> > > I am not advocating what others should do. But I know what I need my
> > > DB to do. If I want it to store data that does not match puritanical
On 16/08/07, Ben <[EMAIL PROTECTED]> wrote:
> On Thu, 16 Aug 2007, Phoenix Kiula wrote:
>
> > I am not advocating what others should do. But I know what I need my
> > DB to do. If I want it to store data that does not match puritanical
> > standards of textual storage, then it should allow me to...
I don't know how PSQL does it, but MySQL has an SQL_CALC_FOUND_ROWS
extension which allows the query to also return how many rows exist without
the LIMIT clause. Perhaps there is similar for PSQL (check LIMIT docs?)
- Andrew
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTEC
On Wed, Aug 15, 2007 at 10:21:31AM +0300, Sabin Coanda wrote:
> Hi there,
>
> I have a procedure which uses temporary objects (table and sequence). I
> tried to optimize it, using common variables (array and long varchar)
> instead. I didn't found any difference in performance, but I'd like to
All,
I have a stored procedure that I use to manage a queue. I want to pop
an item off the queue to ensure that only one server is processing the
queue item, so inside PGSQL, use SELECT ... FOR UPDATE to lock the row.
Here's how I pop the queue item:
---
On Wed, 2007-08-15 at 07:06 +0530, Merlin Moncure wrote:
> You were half right. Inserts in PostgreSQL perform similar to other
> databases (or at least, use similar mechanisms). It's the updates
> that suffer, because this translates to delete + insert essentially.
> Databases that use simple loc
On Sat, Aug 11, 2007 at 02:43:30AM -0500, Javier Fonseca V. wrote:
> Hello.
>
> I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
>
> I think that it's working alright except for the next line:
>
> EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
No.
I have tested all cases, the code I quoted is complete and minimal. All
operations are non-blocking (count incrementation is non-blocking, insertion
with a foreign key is non-blocking too), but it still generates a deadlock
time to time. Deletion of the foreign key constraint completely solves
On Wednesday 15. August 2007, Phoenix Kiula wrote:
>> At least on a *nix system, collation is based on the value of the
>> LC_ALL environment variable at dbinit time. There's nothing you can
>> do about it in a live database. IMO that's a little awkward, and is
>> what finally made me change the gl
On Thu, Aug 16, 2007 at 01:56:52AM +0800, Phoenix Kiula wrote:
> This is very useful, thanks. This would be "bytea"? Quick questions:
>
> 1. Even if it were bytea, would it work with regular SQL operators
> such as regexp and LIKE?
bytea is specifically designed for binary data, as such it has al
On 8/15/07, Jeff Davis <[EMAIL PROTECTED]> wrote:
> On Wed, 2007-08-15 at 07:06 +0530, Merlin Moncure wrote:
> > You were half right. Inserts in PostgreSQL perform similar to other
> > databases (or at least, use similar mechanisms). It's the updates
> > that suffer, because this translates to de
"Decibel!" <[EMAIL PROTECTED]> writes:
> On Wed, Aug 15, 2007 at 01:26:02PM -0400, Steve Madsen wrote:
>> On Aug 15, 2007, at 11:52 AM, Decibel! wrote:
>> >I can't really think of a case where a seqscan wouldn't return all the
>> >rows in the table... that's what it's meant to do.
LIMIT
--
Gr
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
All,
I have a stored procedure that I use to manage a queue. I want to
pop an item off the queue to ensure that only one server is
processing the queue item, so inside PGSQL, use SELECT ... FOR
UPDATE to lock the row. Here's how I pop
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item, so
inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is under severe load, however, this function begins to
take a long time to execu
On Aug 15, 2007, at 13:27 , Naz Gassiep wrote:
Hi all,
I am still, after quite some time, wrangling over the time zone
system in my app. I have sorted out all the internal handling,
however I am still uncertain as to what the best way to get the
user to select their time zone is.
On Aug 15, 2007, at 2:39 PM, [EMAIL PROTECTED] wrote:
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item,
so inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is under severe load, how
Date: Wed, 15 Aug 2007 20:31:45 +0200
From: "Leif B. Kristensen" <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Subject: Re: pg_dump on local Windows, pg_restore on Linux?
Message-ID: <[EMAIL PROTECTED]>
On Wednesday 15. August 2007, Phoenix Kiula wrote:
>
>This is great info, thanks. Could
On 8/15/07, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> Trevor Talbot wrote:
> > On 8/14/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
> >
> >> Let me fine tune my question here. What I mean to say is the way we can
> >> write stored procedures in C, perl etc in Postgres specifying the langu
On 8/15/07, Rohit <[EMAIL PROTECTED]> wrote:
> I have few queries regarding the use of Stored Procedures, Functions
> and Triggers in an RDBMS.
>
> (1) When to use Stored Procedure? Writing an INSERT query in a Stored
> Procedure is better or firing it from the application level?
>
> (2) Can a Trig
The first time that I read it, the work-around sounded a little odd
(extreme!) to me... but actually it would really work!.
Thanks for the tip :)
*Javier*
On 8/15/07, Decibel! <[EMAIL PROTECTED]> wrote:
>
> On Sat, Aug 11, 2007 at 02:43:30AM -0500, Javier Fonseca V. wrote:
> > Hello.
> >
> > I'
On 8/15/07, Rohit <[EMAIL PROTECTED]> wrote:
I have few queries regarding the use of Stored Procedures, Functions
and Triggers in an RDBMS.
(1) When to use Stored Procedure? Writing an INSERT query in a Stored
Procedure is better or firing it from the application level?
(2) Can a Trigger call a
1 - 100 of 119 matches
Mail list logo