Thanks for the quick reply. I made a mistake last time by asking the question:
actually, i would like to know how to get the OID from a table name or operator name.
For example, ">" is 512 while "=" is 96. and some table has the magic relid of 20078, say.
How could I find out the OID by givin
On Fri, 2005-11-11 at 18:36 -0500, [EMAIL PROTECTED] wrote:
> On Fri, Nov 11, 2005 at 06:00:32PM -0500, Rod Taylor wrote:
> > So? That is what save points are for. You can even skip the select for
> > update if you don't mind dead tuples from the attempted insert.
> > SELECT ... FOR UPDATE;
> > IF
try this, i had no data to check the plan and didn't have time to invent any.
Jim
create index idx_archive_jb_idx on
archive_event(inst,utctime,src,bid,tid);
explain
SELECT count(cid) AS hits,src, bid,
tid,
(select MIN(utctime)
from archive_event
where src = ae.src
AND bid =ae.bid
AND tid =
This has been posted to performance but i didin't had any answer i could
look forward...
If anyone got some time for explanation,examples..
Abstract:
The function that can be found at the end of the e-mail emulate two
thing.
First it will fill a record set of result with needed column from a
t
On Fri, Nov 11, 2005 at 06:00:32PM -0500, Rod Taylor wrote:
> So? That is what save points are for. You can even skip the select for
> update if you don't mind dead tuples from the attempted insert.
> SELECT ... FOR UPDATE;
> IF not exists THEN
> SAVEPOINT;
> INSERT ;
> IF UNIQUE
On Fri, 11 Nov 2005, Josh Berkus wrote:
> Jaime,
>
> > why? seems that REPLACE only work if there are at least one row
> > matching...
>
> Scenario:
>
> session1: REPLACE 1
> session2: REPLACE . 1
> session1: check to see that "1" exists no
> sess
Josh Berkus writes:
> But even REPLACE requires predicate locking. There's no real way to get
> around it.
The point though is that REPLACE is restricted to a type of predicate
narrow enough to be enforced through a unique-index mechanism, and so
it's implementable without solving the general c
On Fri, 2005-11-11 at 14:40 -0800, Josh Berkus wrote:
> Jaime,
>
> > why? seems that REPLACE only work if there are at least one row
> > matching...
> Get the picture? The only way to avoid a race condition is to be able to
> do "predicate locking", that is to lock the table against any data wr
Jaime,
> why? seems that REPLACE only work if there are at least one row
> matching...
Scenario:
session1: REPLACE 1
session2: REPLACE . 1
session1: check to see that "1" exists no
session2: check to see that "1" exists no
sessio
Brusser, Michael wrote:
Is there a way to find a number of current connections on Postgres 7.3.x
?
select count(*) from pg_stat_activity
--
Bryan White, ArcaMax Publishing Inc.
I never look back, darling. it distracts from the now. - Edna Mode
---(end of broadcast)---
On 11/11/05, Josh Berkus wrote:
> Guys,
>
> > I understand you well... what i was trying to say is that i prefer
> > MERGE (standard SQL command) to be done because the functionally it
> > has (basically a merge of two tables) seems to me to be more usefull
> > than REPLACE (MySql Command)...
>
>
Tom Lane Wrote:
> Surely they require a unique constraint --- else the behavior
> isn't even well defined, is it?
From the mysql manual:
'REPLACE works exactly like INSERT, except that if an old record in the
table has the same value as a new record for a PRIMARY KEY or a UNIQUE
index, the old
I Wrote:
> From the mysql manual:
>
> 'REPLACE works exactly like INSERT, except that if an old
> record in the table has the same value as a new record for a
> PRIMARY KEY or a UNIQUE index, the old record is deleted
> before the new record is inserted. See Section 13.2.4,
> "INSERT Syntax".
Guys,
> I understand you well... what i was trying to say is that i prefer
> MERGE (standard SQL command) to be done because the functionally it
> has (basically a merge of two tables) seems to me to be more usefull
> than REPLACE (MySql Command)...
But even REPLACE requires predicate locking. T
Matt Newell <[EMAIL PROTECTED]> writes:
> On Friday 11 November 2005 11:07, you wrote:
>> 1. How do you avoid deadlock among multiple processes all doing the
>> above for similar (same page anyway) keys?
> Isn't all that is required is that they iterate through the indexes in the
> same order.
Y
On 11/11/05, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> Jaime Casanova wrote:
> > MERGE seems to me the better option... not just because is standard
> > but at least i can see some use cases for it...
>
> I don't think you understand my message: MERGE does not do what REPLACE
> does.
>
> --
> P
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> It seems to me that it has always been implicitly assumed around here
> that the MERGE command would be a substitute for a MySQL-like REPLACE
> functionality. After rereading the spec it seems that this is not the
> case. MERGE always operates on
Please disregard this duplicate
submission, my mistake.
Mike
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brusser, Michael
Sent: Friday, November 11, 2005
12:35 PM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] How to find a
number of connections
Is
Is there a way to find a number of current connections on
Postgres 7.3.x ?
I looked at some system tables and views, but did not see
anything obvious.
Thank you,
Mike
Jaime Casanova wrote:
> MERGE seems to me the better option... not just because is standard
> but at least i can see some use cases for it...
I don't think you understand my message: MERGE does not do what REPLACE
does.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
-
On Fri, Nov 11, 2005 at 15:09:41 -0500,
"Brusser, Michael" <[EMAIL PROTECTED]> wrote:
> Is there a way to find a number of current connections on Postgres 7.3.x
> ?
This might help you:
http://www.postgresql.org/docs/7.3/static/monitoring.html
---(end of broadcast)--
On 11/11/05, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> It seems to me that it has always been implicitly assumed around here
> that the MERGE command would be a substitute for a MySQL-like REPLACE
> functionality. After rereading the spec it seems that this is not the
> case. MERGE always ope
Is there a way to find a number of current connections on Postgres 7.3.x
?
Thank you,
Mike
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Friday 11 November 2005 11:07, you wrote:
> It's an idea, but you are now staring directly into the hornet's nest:
>
> 1. How do you avoid deadlock among multiple processes all doing the
>above for similar (same page anyway) keys? It's difficult if not
>impossible to ensure that they'l
Peter Eisentraut wrote:
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which
In article <[EMAIL PROTECTED]>,
Martijn van Oosterhout writes:
> It's even sillier than that:
> test=# SELECT substring ('1234567890' FOR 4::bigint);
> substring
> ---
> (1 row)
> test=# SELECT substring ('1234567890' FOR 4::int);
> substring
> ---
> 1234
> (1 row)
> Look
It seems to me that it has always been implicitly assumed around here
that the MERGE command would be a substitute for a MySQL-like REPLACE
functionality. After rereading the spec it seems that this is not the
case. MERGE always operates on two different tables, which REPLACE
doesn't do.
Tha
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Surely they require a unique constraint --- else the behavior isn't
>> even well defined, is it?
> They require that the merge condition does not match for more than one
> row, but since the merge condition can do just about anythi
On Fri, Nov 11, 2005 at 18:48:33 +0100,
Csaba Nagy <[EMAIL PROTECTED]> wrote:
> OK, I'm relatively new on this list, and I might have missed a few
> discussions on this topic.
> I wonder if doing it this way would not be better than using a table
> lock:
>
> - set a save point;
> - insert the
Jaime Casanova wrote:
> REPLACE will delete old records to insert new ones; MERGE try to
> insert and if the record exists then can UPDATE just a few values,
> maybe incrementing them with a value (all the calculation are doing
> by the MERGE)
I'm not the expert on REPLACE, but it would seem that
Matt Newell <[EMAIL PROTECTED]> writes:
> Would it be possible to make another routine that locates and aquires
> a write lock on the page where the key would be inserted in each
> index(for each table in the inheritance), and holds all these locks
> until the key is inserted into the correct index
Jaime Casanova Wrote:
> But MERGE isn't REPLACE...
>
> REPLACE will delete old records to insert new ones; MERGE try
> to insert and if the record exists then can UPDATE just a few
> values, maybe incrementing them with a value (all the
> calculation are doing by the MERGE)
That sounds like
Tom Lane wrote:
> Surely they require a unique constraint --- else the behavior isn't
> even well defined, is it?
They require that the merge condition does not match for more than one
row, but since the merge condition can do just about anything, there is
no guarantee that a unique constraint e
On 11/11/05, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> Tom Lane wrote:
> > If you don't have any better idea how to do it than a full table
> > lock, you might as well not do it at all. A "proof of concept" that
> > doesn't solve the hard part of the problem is no proof :-(
>
> But the problem
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> This assumes that there are indexes defined for the columns involved in
> the merge condition, which is not required anywhere.
Surely they require a unique constraint --- else the behavior isn't even
well defined, is it?
rega
huaxin zhang napisał(a):
Hi all,
I am interested in the answer as well -- how to get a table name (or
an operator name)
from an OID.the parser must know how to do this, but the segment
of code is hard
to locate.
CREATE OR REPLACE FUNCTION gettablename(__oid oid)
RETURNS "varchar" AS
$BOD
Tom Lane wrote:
> If you don't have any better idea how to do it than a full table
> lock, you might as well not do it at all. A "proof of concept" that
> doesn't solve the hard part of the problem is no proof :-(
But the problem here is not to break any kind of performance barrier,
but to give
On Thursday 10 November 2005 15:58, you wrote:
> >> The multi-table-unique-constraint problem has to
> >> be solved before we can even think much about multi-table FKs :-(
> >
> > Do you have ideas about how this should be solved?
>
> There's some discussions in the pghackers archives --- look for
OK, I'm relatively new on this list, and I might have missed a few
discussions on this topic.
I wonder if doing it this way would not be better than using a table
lock:
- set a save point;
- insert the row;
- on error:
- roll back to the save point;
- update the row;
-
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 11 Nov 2005, Tom Lane wrote:
>> I was toying with the idea of making it translate instead to
>>
>> pg_catalog.substring(foo, 1, (bar)::int4)
>>
>> since AFAICS there isn't any other reasonable mapping once you have
>> committed to having the "1"
Josh Berkus writes:
> Funny, we were just discussing this at OpenDBCon. Seems that you can't do a
> full implementation of MERGE without Predicate Locking (the ability to say
> "lock this table against inserts or updates of any row with key=5").
> However, Peter suggested that we could do a p
Josh Berkus wrote:
> Funny, we were just discussing this at OpenDBCon. Seems that you
> can't do a full implementation of MERGE without Predicate Locking
> (the ability to say "lock this table against inserts or updates of
> any row with key=5"). However, Peter suggested that we could do a
> proo
On Fri, 11 Nov 2005, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > It looks to me like we should be supporting any exact numeric with scale 0
> > there (at least AFAICS from SQL92 and SQL03), so I don't think the current
> > behavior is compliant. It doesn't look like adding a nu
On Fri, 2005-11-11 at 18:15, Jaime Casanova wrote:
> On 11/11/05, Josh Berkus wrote:
> > Jaime,
> >
> > > so i suppose we can reuse many of the code breaking the merge in 3
> > > pieces... for now they are just thougths, i will think more in this
> > > and try to implement it...
> > >
> > > commen
Jaime Casanova wrote:
Funny, we were just discussing this at OpenDBCon. Seems that you can't do a
full implementation of MERGE without Predicate Locking (the ability to say
"lock this table against inserts or updates of any row with key=5").
it isn't what select for update does?
On 11/11/05, Josh Berkus wrote:
> Jaime,
>
> > so i suppose we can reuse many of the code breaking the merge in 3
> > pieces... for now they are just thougths, i will think more in this
> > and try to implement it...
> >
> > comments? ideas? suggestions?
>
> Funny, we were just discussing this at
Jaime,
> so i suppose we can reuse many of the code breaking the merge in 3
> pieces... for now they are just thougths, i will think more in this
> and try to implement it...
>
> comments? ideas? suggestions?
Funny, we were just discussing this at OpenDBCon. Seems that you can't do a
full impl
Martijn van Oosterhout writes:
> In this particular case the syntax makes it unclear that the substring
> is the problem. Perhaps here the solution would be to put a cast in the
> grammer, like so:
> substr_for: FOR a_expr { $$ =3D
> makeTypeCast($2,"int4"); }
>
Stephan Szabo <[EMAIL PROTECTED]> writes:
> It looks to me like we should be supporting any exact numeric with scale 0
> there (at least AFAICS from SQL92 and SQL03), so I don't think the current
> behavior is compliant. It doesn't look like adding a numeric overload
> of the function works, and th
On Fri, Nov 11, 2005 at 07:47:12AM -0800, Stephan Szabo wrote:
>
> On Fri, 11 Nov 2005, Tom Lane wrote:
> > This has been complained of before. The problem is that there is no
> > implicit cast from bigint to int, but there is one from bigint to text,
> > so the only acceptable mapping the parser
On Fri, 11 Nov 2005, Tom Lane wrote:
> Martijn van Oosterhout writes:
> > It's even sillier than that:
>
> > test=# SELECT substring ('1234567890' FOR 4::bigint);
> > substring
> > ---
> >
> > (1 row)
>
> > test=# SELECT substring ('1234567890' FOR 4::int);
> > substring
> > --
Tom Lane wrote:
"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
What about having the calling code fill in the io type oid in an extra field
in the flinfo?
I don't think that's workable; for one thing there's the problem of
manual invocation of the I/O functions, which is not going to
Martijn van Oosterhout writes:
> It's even sillier than that:
> test=# SELECT substring ('1234567890' FOR 4::bigint);
> substring
> ---
>
> (1 row)
> test=# SELECT substring ('1234567890' FOR 4::int);
> substring
> ---
> 1234
> (1 row)
This has been complained of before.
Tom Lane wrote:
Martijn van Oosterhout writes:
On Fri, Nov 11, 2005 at 11:05:45AM +0100, Matteo Beccati wrote:
It seems that the plan outputted is not the optimized one (available
since 8.1) that is really used when running the plain query.
It may also be that the overhead of calling gettim
Martijn van Oosterhout writes:
> On Fri, Nov 11, 2005 at 11:05:45AM +0100, Matteo Beccati wrote:
>> It seems that the plan outputted is not the optimized one (available
>> since 8.1) that is really used when running the plain query.
> It may also be that the overhead of calling gettimeofday() sev
Martijn van Oosterhout writes:
> On Thu, Nov 10, 2005 at 11:29:15PM +0100, Olivier Thauvin wrote:
>> To fix the issue, I will add a provide into postgresql-server package,
>> something like postgresql-ABI =3D 8.1, add equivalent requires into modules.
> There is some discussion about this on -hac
On Thu, Nov 10, 2005 at 11:53:04PM +0100, Guillaume LELARGE wrote:
> Hi,
>
> I've installed a 8.1.0 PostgreSQL server on a SCO OpenServer 5.0.6. It
> seemed to work well with psql and such tools. I tried to connect to
> this server with pgAdmin3 and a query failed. I tried to find which
> part of
It's even sillier than that:
test=# SELECT substring ('1234567890' FOR 4::bigint);
substring
---
(1 row)
test=# SELECT substring ('1234567890' FOR 4::int);
substring
---
1234
(1 row)
Looking at the explain verbose make it look like it's using the wrong
version of substring
Hi,
I've installed a 8.1.0 PostgreSQL server on a SCO OpenServer 5.0.6. It
seemed to work well with psql and such tools. I tried to connect to
this server with pgAdmin3 and a query failed. I tried to find which
part of the query was wrong and I have a strange result :
SELECT 1 FROM pg_language WH
On Wed, Nov 02, 2005 at 06:31:56PM +0100, Steinar H. Gunderson wrote:
> I was asked to post this here for any interested parties -- please Cc me on
> any comments/followups as I'm not subscribed to -hackers.
...and here's a version with another algorithm, in PL/Perl (in PL/PgSQL, the
same algorith
On Fri, Nov 11, 2005 at 08:37:07AM -0500, huaxin zhang wrote:
> Hi all,
>
> I am interested in the answer as well -- how to get a table name (or
> an operator name) from an OID. the parser must know how to do this,
> but the segment of code is hard to locate.
For the purposes of error messages,
Hi,
there is someone working in add the MERGE statement?
i don't find much about what a good implementation of merge must have...
i think what it needs to do is something like:
- try to lock the rows for update
- if the lock cannot be immediatly acquire ask why
- if the rows are alread
> "Merlin Moncure" <[EMAIL PROTECTED]> writes:
> > When the dump gets to the point where the indexes/keys are built,
the
> > primary key fails to build due to duplicate key constraint failure.
> > However, after dump is complete, I can create the p-key without any
> > modification to the table and
Consider the following:
CREATE TEMP TABLE tbl (
id SERIAL NOT NULL,
PRIMARY KEY (id)
);
COPY tbl (id) FROM stdin;
1
2
3
4
\.
SELECT substring ('1234567890' FOR (SELECT count (*) FROM tbl)::int);
This returns '1234', as expected. But
SELECT substring ('1234567890' F
Hi all,
I am interested in the answer as well -- how to get a table name (or
an operator name)
from an OID.the parser must know how to do this, but the segment
of code is hard
to locate.
thanks a lot,
Huaxin
On 11/7/05, Paresh Bafna <[EMAIL PROTECTED]> wrote:
> Actually I want to do this fr
Gavin Sherry wrote:
> I am at OpenDBCon in Germany. People are complaining about
> floats/numerics not accepting German/European conventions for the
> delimiter point -- a comma.
Supporting localized input formats everywhere (numbers, geometric
objects, date/time, arrays, etc.) will create dozens
Martijn van Oosterhout wrote:
I've noticed that sometimes EXPLAIN ANALYZE is much slower than the
plain query. After investigating I found that it happens when using MIN
or MAX aggregates.
It seems that the plan outputted is not the optimized one (available
since 8.1) that is really used when
On Fri, Nov 11, 2005 at 11:05:45AM +0100, Matteo Beccati wrote:
> Hi,
>
> I've noticed that sometimes EXPLAIN ANALYZE is much slower than the
> plain query. After investigating I found that it happens when using MIN
> or MAX aggregates.
>
> It seems that the plan outputted is not the optimized
Hi all, I just installed PostgreSQL 8.1 win32 and didn't find option to
install contrib/xml2, is it available on win32? Shall I build it on my own?
Thanks in advance
Paolo
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Hi,
I've noticed that sometimes EXPLAIN ANALYZE is much slower than the
plain query. After investigating I found that it happens when using MIN
or MAX aggregates.
It seems that the plan outputted is not the optimized one (available
since 8.1) that is really used when running the plain query.
On Thu, Nov 10, 2005 at 11:29:15PM +0100, Olivier Thauvin wrote:
> I just make postgresql 8.1 rpm for mandriva and I was making basic test. I
> made in pgfoundry the module pgrpm (rpm function in postgres).
> Well the problem here is the module was build with postgresql 8.0. I will
> rebuild t
> > > > Hello,
> > > > how can i make a checkout from CVS server ? What is the address?
> > >
> > > You can find all the information you need to know about
> how to check
> > > pgsql out of cvs at:
> http://www.postgresql.org/developer/sourcecode/
> >
> > Also if you are looking for a pretty i
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: 10 November 2005 15:38
> To: Dave Page
> Cc: Andrew Dunstan; PostgreSQL-development
> Subject: Re: [HACKERS] pg_proc.h
>
> "Dave Page" writes:
> > I vote for fixing the file (but then I'm not doing the work).
> >
73 matches
Mail list logo
