Richard Ellis <[EMAIL PROTECTED]> writes:
> True, if the row does not already exist. But in that situation,
> because of the unique constraint premise in the original quote, there
> is always going to be at least one failed transaction. So the battle
> is already lost before it's even begun.
W
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Wed, 3 Sep 2003, Bupp Phillips wrote:
>
> > Well, it's unfortunate that you feel that way, because SQL Server handles it
> > correctly.
>
> For some definition of correctly. If you're in a system which gets
> penalized .001 seconds for each query
Jan Wieck wrote:
> Tom Lane wrote:
>
> > Jan Wieck <[EMAIL PROTECTED]> writes:
> >> Okay, my proposal would be to have a VACUUM mode where it tells the
> >> buffer manager to only return a page if it is already in memory, and
> >> some "not cached" if it would have to read it from disk, and simp
Manfred Koizar wrote:
> [ still brainstorming ... ]
>
> On Thu, 21 Aug 2003 17:16:50 -0400, Tom Lane <[EMAIL PROTECTED]>
> wrote:
> >> Whenever a backend encounters a dead tuple it inserts a reference to
> >> its page into the RSM.
> >
> >This assumes that backends will visit dead tuples with sign
Whatever you guy's try or suggest, it's doomed to suffer.
The whole problem stems from using a non-standard feature. And in my
opinion MySQL's "REPLACE INTO" is less a feature or extension to the
standard than more another stupid and lesser thought through addition of
apparently speed gaining c
Alvaro Herrera Munoz <[EMAIL PROTECTED]> writes:
> Anyway, I think the LOCK structure represents something that can be locked.
Right.
> The PROCLOCK struct represents that some process is holding a lock on said
> object.
IIRC, a PROCLOCK is created as soon as some backend tries to lock some
lock
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Thu, 4 Sep 2003, Bupp Phillips wrote:
>> I can't find a way to create descending Indexes in PG. Is this possible?
> You can do it, but it's a bit difficult.
Do you really *need* a descending index? In most cases a backwards scan
on a regular index g
On Thu, 4 Sep 2003, Sean Chittenden wrote:
> > yes it is/was ...I've just removed it from the list of stop words,
> > but am not going to force a re-index of 250k URLs ;( So, any new
> > index'ng will pull in index as a valid search word, and when the
> > current URLs expire/re-index,they will g
> yes it is/was ...I've just removed it from the list of stop words,
> but am not going to force a re-index of 250k URLs ;( So, any new
> index'ng will pull in index as a valid search word, and when the
> current URLs expire/re-index,they will get pulled in then also ...
Hrm... if I search for "co
yes it is/was ...I've just removed it from the list of stop words, but am
not going to force a re-index of 250k URLs ;( So, any new index'ng will
pull in index as a valid search word, and when the current URLs
expire/re-index,they will get pulled in then also ...
On Thu, 4 Sep 2003, Sean Chitt
You want this document:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
great article on performance tuning postgresql. After reading it and
digesting all you can, you should have a noticeably faster machine.
On Thu, 4 Sep 2003, Edwin Quijada wrote:
> ok!
> U can tell me about th
Richard Huxton schrieb:
On Wednesday 03 September 2003 12:19, Amin Schoeib wrote:
Hi,
How can I make textoutput (for example for tracing the code) in PLPGSQL.
In Oracle you can do that using dbms.output.put_line
RAISE NOTICE ''My counter is % with total %'',i,run_tot;
Double-quoted string as u
On Thu, 2003-09-04 at 15:40, Doug McNaught wrote:
> Bjørn T Johansen <[EMAIL PROTECTED]> writes:
>
> > Should one use pg_dumpall to backup the database or is it more practical
> > to just copy the data directory?
>
> The data directory will not be consistent unless the server is
> stopped. pg_du
On Thu, Sep 04, 2003 at 12:29:17PM -0700, Stephan Szabo wrote:
>
> On Thu, 4 Sep 2003, Richard Ellis wrote:
>
> > On Thu, Sep 04, 2003 at 12:17:35PM +0200, Csaba Nagy wrote:
> > > [philosophical post regarding a missing feature of Postgres]
> > >
> > > I found there's no way to avoid failed inser
On Thu, 2003-09-04 at 14:08, Edwin Quijada wrote:
> ok!
> U can tell me about the best setup and performance?
> and best parameters to setup I am so newbie ?
> I am using Postgres to emigrate a system in 4D Server to PgSql With Delphi
> for insurance company.
> My server is Pentium IV 2.0 Ghz , 1
So I have a query in which some of the select values are
subqueries. The
subqueries are aggregates so I don't want to turn this into a join,
it would
become too complex and postgres would have trouble optimizing things.
So my question is, is there some way to have a subselect return
multiple
c
Bjørn T Johansen <[EMAIL PROTECTED]> writes:
> Should one use pg_dumpall to backup the database or is it more practical
> to just copy the data directory?
The data directory will not be consistent unless the server is
stopped. pg_dumpall works well, produces a consistent backup and is
easily por
> "BTJ" == Bjørn T Johansen writes:
BTJ> Should one use pg_dumpall to backup the database or is it more
yes, or pg_dump specific databases.
BTJ> practical to just copy the data directory?
this would do you no good, since the files are not necessarily in a
consistent state when you copy man
ok!
U can tell me about the best setup and performance?
and best parameters to setup I am so newbie ?
I am using Postgres to emigrate a system in 4D Server to PgSql With Delphi
for insurance company.
My server is Pentium IV 2.0 Ghz , 1 GB Ram, 40 GB HD 7200RPM?
Now I wanna know what is the best s
On Thu, Sep 04, 2003 at 11:21:05AM -0700, Jenny - wrote:
> >I think the locks would actually by represented by PROCLOCK structures.
> >The LOCK structures are for lockable objects, not for actual locks.
>
> Well,from what i understand, PROCLOCK stores the TransactionID and the LOCK
> its holding
Is "index" a stop word in the archives? It doesn't seem to return any
results when typing in "index" with anything (ex: index correlation).
FWIW, just a data point. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 8: explain analyze is your fr
Ian Harding wrote:
A tcl function is just like any other, you can call it from within a
plpgsql trigger function in the same way you would call a built-in, or
any other function. Having said that, I am not sure what the exact
plpgsql options are besides "SELECT myfunction(' 'foo' ', ' 'bar'
However, that doesn't cover the case where you want to update the record
if it already exists.
Jon
> insert into test (a, b, c, d)
> (select 1, 2, 3, 4 where not exists
> (select 1 from test where a=1 and b=2 and c=3 and d=4)
> );
>
> If your table contains a=1, b=2, c=3, and d=4, nothing
Hi Jan,
You are right, that's why the loop doesn't goes through all the columns.
Thanks.
Jan Wieck wrote:
The arrays NEW and OLD only contain entries for attributes in the
respective rows that are not NULL. The list $TG_relatts contains a list
of all column names of the triggering relation wi
On Wed, 3 Sep 2003, Bupp Phillips wrote:
> Well, it's unfortunate that you feel that way, because SQL Server handles it
> correctly.
For some definition of correctly. If you're in a system which gets
penalized .001 seconds for each query planning that uses a multi-column
order by and you do 100
Adam Kavan wrote:
At 10:48 AM 9/4/03 -0400, Matthew T. O'Connor wrote:
On Thu, 2003-09-04 at 09:01, Jan Wieck wrote:
> Do the stats_* options appear twice in the config file? If that isn't
> the case, the only possible problem left I can think of is that you're
> running on a platform where no A
On Thu, 4 Sep 2003, Richard Ellis wrote:
> On Thu, Sep 04, 2003 at 12:17:35PM +0200, Csaba Nagy wrote:
> > [philosophical post regarding a missing feature of Postgres]
> >
> > I found there's no way to avoid failed inserts because of
> > unique constraint violations, causing automatic roll-back o
On Thu, 4 Sep 2003, Bupp Phillips wrote:
> I can't find a way to create descending Indexes in PG. Is this possible?
>
> If so, can you point me in the right direction?
> If not, can I request this to be an enhancement?
You can do it, but it's a bit difficult. You need to make an operator
class
Should one use pg_dumpall to backup the database or is it more practical to just copy the data directory?
Regards,
BTJ
--
---
Bjørn T Johansen (BSc,MNIF)
Executive Manager
[EMAIL PROTECTED]
Josué Maldonado wrote:
I'm sorry, found my mistake, the way I'm calling the function is wrong
it should be "audit_log" ('xtable','internal'), it works.
Hmmm, that should not work. The function should be declared taking no
arguments and you yet find the arguments from the CREATE TRIGGER in the
close the connection and reconnect to get a fresh backend that doesn't
cache anything.
Jan
Josué Maldonado wrote:
Hello list,
The TCL trigger that uses NEW and OLD arrays failed after after I
removed a unused column, now I got this error:
pltcl: Cache lookup for attribute 'pg.dropped
The arrays NEW and OLD only contain entries for attributes in the
respective rows that are not NULL. The list $TG_relatts contains a list
of all column names of the triggering relation with a leading empty
element (to make the index in that list reflect pg_attribute.attnum).
Jan
[EMAIL PROTECT
At 02:25 PM 9/4/03 -0400, Jan Wieck wrote:
>H ... would need to take a look at the code ... but a wild guess would
>be the communication between the backends and the collector daemon. I'm
>not sure if the UDP crap^H^H^H^Hstuff I used in there is IPV6 safe. What
>version of PostgreSQL was that?
I spent this aft enabling debugging on the postmaster side, and determined
that there was a critical index missing on one of the tables ... there was
no index on the url.rec_id field, so a query that looks like:
SELECT rec_id, site_id, pop_rank FROM url WHERE rec_id IN
('31356','31364','32786','3
Well, it's unfortunate that you feel that way, because SQL Server handles it
correctly.
"Tom Lane" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Greg Stark <[EMAIL PROTECTED]> writes:
> > "Bupp Phillips" <[EMAIL PROTECTED]> writes:
> >> select * from customer order by customer_id,
It seems to work for me...
create table foobar (
col1varchar,
col2varchar)
;
create function testfunc() returns trigger language pltcl as '
foreach id [array names NEW] {
elog NOTICE $NEW($id)
}
';
create trigger testtrig after insert or update on foobar for each row
exec
Great! Many thanks.
Casting the type did the trick.
Even though it is somehow clear now, I don't see why the type is (can)
not (be) inferred from the field type.
--
Carlos G Mendioroz <[EMAIL PROTECTED]> LW7 EQI Argentina
---(end of broadcast)---
On Thu, Sep 04, 2003 at 08:56:31AM -0700, Jenny - wrote:
> I am working on a project that involves displaying locking information
> about each lock taken, whether it be a row level or table leve llock.
> When dealing with struct LOCK (src/include/storage) i have noticed that
> postgreSQL creates
On Sun, 31 Aug 2003, Jefim M wrote:
>
> I have the following setup for my PostgreSQL box:
> 4GB Ram
> Dual CPU
> Redhat 7.3 with 2.4.20-18.7smp kernel
> 2 network cards - one is dedicated to internal traffic to the NetApp filer
> and other is used for other traffic
>
> and all the Postgres data
[EMAIL PROTECTED] writes:
> #0 SN_create_env (S_size=0, I_size=2, B_size=1) at api.c:6
> 6 z->p = create_s();
Hm. Is it possible you're running out of memory? If the crash is right
there, and not inside create_s(), it seems like a null return from calloc
is the only explanation. This
I am working on a project that involves displaying locking information about
each lock taken, whether it be a row level or table leve llock.
When dealing with struct LOCK (src/include/storage) i have noticed that
postgreSQL creates a single LOCK struct for each table in the db. Like if
i acqu
Harald Fuchs <[EMAIL PROTECTED]> writes:
> You can't. The only thing MySQL has to offer is
Actually I found two things related to this:
http://www.mysql.com/doc/en/INSERT.html
http://www.mysql.com/doc/en/REPLACE.html
You can do
INSERT INTO tab (...) VALUES (...) ON DUPLICATE KEY UPDATE col
First - apologies for the stuff about "i don't understand why there's
only one core file", i now have a post-it note now saying "ulimit gets
reset at reboot" (i assume thats what happened).
So please find below a potentially more useful core file gdb output:
Core was generated by `postgres: mat
Alvaro,
Actually I build a new table from the old one, so the table in metion is
a new table with the same structure as before, the way I droped the
column was right click on it from pgadmin III then delete, why should
that matter?
Thanks
Alvaro Herrera Munoz wrote:
On Thu, Sep 04, 2003 at
> [EMAIL PROTECTED] writes:
> > I have set "ulimit -c unlimited" as you sugested,
> > i then copied postmaster to /home/postgres
> > and ran it as postgres from there...
> > but still no core files. Where should they appear?
>
> In $PGDATA/base/yourdbnumber/core (under some OSes the file name
mi
Alvaro Herrera Munoz <[EMAIL PROTECTED]> writes:
> It would help if you send the complete example, including the part
> where you actually drop a column from the table.
Oh, I forgot about that part (should read the Subject: line again ;-)).
I do see a failure after dropping a column. I've applie
On Mon, 1 Sep 2003, Tom Lane wrote:
> "Claudio Lapidus" <[EMAIL PROTECTED]> writes:
> > So? Is there a way to add the sequence to an existing table?
>
> Sure. You have to break the SERIAL down to its component parts though.
> Something like
>
> CREATE SEQUENCE seq;
> ALTER TABLE tab
On Thu, Sep 04, 2003 at 09:01:08AM -0600, Josu? Maldonado wrote:
> Tom,
>
> This is my table definition:
[etc]
It would help if you send the complete example, including the part
where you actually drop a column from the table.
--
Alvaro Herrera (<[EMAIL PROTECTED]>)
"La victoria es para quien s
What query are you running?
If you run
select * from indexed_table
you'll get a seq scan because you're hitting every row.
If you're doing
select * from indexed_table where indexed_field=1
you should get an index scan.
Anything in between the query planner will decide. Unlike some older
da
Tom,
This is my table definition:
-- Table: public.tinv
-- DROP TABLE public.tinv;
CREATE TABLE public.tinv
(
dep_codigo varchar(6) NOT NULL,
dep_nombre varchar(35),
dep_cantid int4,
dep_fecadq date,
dep_vidaut int4,
dep_pordep int4,
dep_valdep float4,
dep_acumul float4,
dep_t
-BEGIN PGP SIGNED MESSAGE-
Could anyone explain about the max_connections in postgres?
We ran into the connection limit on one of our production servers. One reason
is that we have more apps that use postgres running on it (RT3 and Webcollab
were added not long ago). Webcollab, for inst
=?ISO-8859-1?Q?Josu=E9_Maldonado?= <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Hard to believe. Could you give us a complete example, not a partial
>> one?
> This is the code in the trigger function:
I tried this with a table created like so:
regression=# create table tinv (
regression(# f
Hi [EMAIL PROTECTED]
There is a something way to know which are the IP from conexcion to Postgres
since plPgsql
something like CURRENT_IP
*---*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas e
Hi Tom,
Tom Lane wrote:
=?ISO-8859-1?Q?Josu=E9_Maldonado?= <[EMAIL PROTECTED]> writes:
Noticed that the loop does not go through all fields:
Hard to believe. Could you give us a complete example, not a partial
one?
This is the code in the trigger function:
-- Function: public.audit_log()
--
On Thu, 2003-09-04 at 15:52, Greg Stark wrote:
>
> Csaba Nagy <[EMAIL PROTECTED]> writes:
>
> > This problem would be easily solved if the current transaction would not
> > be automatically rolled back on the failed insert. Given this, it would
> > be as easy as trying the insert, and if fails, d
Well , My table has 38 records!!!
From: "Mattias Kregert" <[EMAIL PROTECTED]>
To: "Edwin Quijada" <[EMAIL PROTECTED]>
Subject: Re: [GENERAL] Activate Index
Date: Thu, 4 Sep 2003 15:58:33 +0200
Hello Edwin,
The index will be used automatically when you have enough rows in the
table. For tabl
[EMAIL PROTECTED] writes:
> I have set "ulimit -c unlimited" as you sugested,
> i then copied postmaster to /home/postgres
> and ran it as postgres from there...
> but still no core files. Where should they appear?
In $PGDATA/base/yourdbnumber/core (under some OSes the file name might
be "core" p
> From: Tom Lane <[EMAIL PROTECTED]>
>
> [EMAIL PROTECTED] writes:
> > How do i get the core files to examine? There never seem to be any
> > produced, even outside the debuggers.
>
> Most likely you have launched the postmaster under "ulimit -c 0",
which
> prevents core dumps. This seems to
Hi!!
This is my first post to the list. I have a problem.
I have a table with a two index , int4, but when I do a search using the
field indexed but when I applied an explain to the query it says Seq Scan ,
I mean , that it doesnot use the index.
I cant use the cast field::int4 because I use delp
Andreas Muck <[EMAIL PROTECTED]> writes:
> We have an application running on Linux (SuSE 7.2, kernel 2.4.16) that
> opens lots of connections to a Postgres database and occasionaly dies
> with segfault.
> The program seems to crash when it returns from pqWaitTimed(). As
> pqWaitTimed uses selec
Csaba Nagy <[EMAIL PROTECTED]> writes:
> This problem would be easily solved if the current transaction would not
> be automatically rolled back on the failed insert. Given this, it would
> be as easy as trying the insert, and if fails, do the update.
Yeah, that would be nested transactions, it'
Tom Lane <[EMAIL PROTECTED]> writes:
> Um ... I *am* an optimizer-geek.
That explains so much :)
> I stand by my comment that there's a tradeoff between the potential gain
> from an optimization and the time spent to find it.
Well there are always tradoffs in engineering. I'm just trying to p
Jules Alberts wrote:
Op 26 Aug 2003 (12:38), schreef Robert Treat <[EMAIL PROTECTED]>:
On Tue, 2003-08-26 at 07:28, Jules Alberts wrote:
> Hello everyone,
>
> I'm working on a tiny trigger function that needs to ensure that all
> values entered in a field are lowercase'd. I can't use pl/pgsql
>
On Thu, 4 Sep 2003, Teodor Sigaev wrote:
>
>
> Nigel J. Andrews wrote:
> >
> > I don't seem to be able to find the mailing list thread that covered pretty
> > much this exact unexpect exit fault. So, can anyone help with a fix,
> > explanation or link to the relevent thread please?
>
> Have yo
Nigel J. Andrews wrote:
This will be a little vague, it was last night and I can't now do the test in
that db (see below) so can't give the exact wording.
I seem to remember a report a little while ago about tsearch v2 causing
unexpected backend exit messages with 7.3.4 and now I'm getting simila
This will be a little vague, it was last night and I can't now do the test in
that db (see below) so can't give the exact wording.
I seem to remember a report a little while ago about tsearch v2 causing
unexpected backend exit messages with 7.3.4 and now I'm getting similar
messages unpredictabl
This problem is discussed on erserver-general. There are some patches
available, that solves the problem and Andrew Sullivan posted workarounds
for those didn't get their
tables replicated. If you're not subscribed already, you can read the
archive here:
http://gborg.postgresql.org/pipermail/er
>
> On 03/09/2003 22:56 Bjørn T Johansen wrote:
>> Oki, thx... Any ideas to what is sensible values also?
>> (Like number of maxIdle compared to maxActive, etc..)
>>
>
> I think sensible values are going to be very dependent on the number of
> requests. One way to find out might be to write a littl
On 4 Sep 2003 at 12:17, Csaba Nagy wrote:
> This problem would be easily solved if the current transaction would not
> be automatically rolled back on the failed insert. Given this, it would
> be as easy as trying the insert, and if fails, do the update.
That violates definition of a transaction.
On Thu, 4 Sep 2003, Tom Lane wrote:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Tom Lane <[EMAIL PROTECTED]> writes:
> >> Yes, that's the real crux of the matter. Should the optimizer spend
> >> cycles on *every* query to detect cases where the user has written
> >> useless sort keys? I've got
[philosophical post regarding a missing feature of Postgres]
Hi all,
This is exactly the problem I'm facing right now, and I found there's no
good solution to this in postgres.
Basically I have a complex database operation, which spans a transaction
across multiple simple DB operations which can
71 matches
Mail list logo
