On Monday, Aug 18, 2003, at 09:01 US/Pacific, <[EMAIL PROTECTED]>
wrote:
With those items in mind, your function could become:
CREATE FUNCTION key_generation(integer, varchar(20)) RETURNS integer
AS'
DECLARE
the_department ALIAS FOR $1;
the_table_name ALIAS FOR $2;
BEGIN
IF NOT EXISTS(SELE
On Monday, Aug 18, 2003, at 07:27 US/Pacific, Vilson farias wrote:
Greetings,
I'm getting a big performance problem and I would like to ask you
what
would be the reason, but first I need to explain how it happens.
Let's suppose I can't use sequences (it seams impossible but my boss
doesn't
Stephan Szabo <[EMAIL PROTECTED]> writes:
> I can't really think of any other way to interpret that section
> particularly differently. If it's a simple table query and the expression
> is not equivalent to a select list item then it can't use distinct or
> group by or a set function.
But this is
Jeffrey Melloy <[EMAIL PROTECTED]> writes:
> The docs say that 'now' is turned into a constant right away. Is this
> overhead/poor planning simply because 'now' gets converted to a
> constant so much earlier in the process?
Yes. Note the estimated numbers of rows in the different plans. In
On Mon, 18 Aug 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > Okay, I think many of the random restrictions (in 2a, the grouping,
> > distinct, set function spec) are to stop you from doing things like:
>
> > select distinct a from table order by b;
> > select a,min(b) fro
I was recently running into performance problems with a query
containing now()::date or CURRENT_DATE. When I went to debug,
'now'::date made efficient use of the index (on a timestamp field).
The docs say that 'now' is turned into a constant right away. Is this
overhead/poor planning simpl
Stephan Szabo <[EMAIL PROTECTED]> writes:
> Okay, I think many of the random restrictions (in 2a, the grouping,
> distinct, set function spec) are to stop you from doing things like:
> select distinct a from table order by b;
> select a,min(b) from table group by a order by c;
> select count(*)
On Mon, 18 Aug 2003 [EMAIL PROTECTED] wrote:
> Here's what I have (simplified)
>
> SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
> FROM t1, t2, t3
> WHERE t1.fid = X
> AND t2.vid = Y
> AND t3.fid = t1.fid
> AND t3.vid = t2.vid
>
> Now, I discover that the record in t3 may not al
On Mon, 18 Aug 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > -- This seems really different from our previous standard reading of SQL92
> > though. It implies that you can't really do stuff on input columns
> > except in very limited cases and that'd be really bad.
>
> Ye
Here's what I have (simplified)
SELECT t1.fid, t1.t1_data, t2.vid, t2.t2_data, t3.t3_data
FROM t1, t2, t3
WHERE t1.fid = X
AND t2.vid = Y
AND t3.fid = t1.fid
AND t3.vid = t2.vid
Now, I discover that the record in t3 may not always exist, so somehow I
want to do an outer join...
SELECT t1.f
because the people who created it had doctorate degrees? kidding:-)
Tom Lane wrote:
Stephan Szabo <[EMAIL PROTECTED]> writes:
-- This seems really different from our previous standard reading of SQL92
though. It implies that you can't really do stuff on input columns
except in very limited case
Stephan Szabo <[EMAIL PROTECTED]> writes:
> -- This seems really different from our previous standard reading of SQL92
> though. It implies that you can't really do stuff on input columns
> except in very limited cases and that'd be really bad.
Yes, it seems fraught with bogus restrictions, which
Thank you for the information.
> the easy way to do that is to create an alternate pg_hba.conf in the
> $PGDATA directory that only allows the postgresql superuser to attach and
> then restart postgresql with that pg_hba.conf in place.
By that you mean to have a pg_hba.conf file in $PGDATA direct
I am rather new to PostgreSQL.What I am looking for ist this:
An example database that demonstrates the usage of all the core
PostgreSQL features. It is one thing to read extensive documentation,
but it is much more intuitive for me to see an example database, that
demonstrates the whole thing
On Mon, 18 Aug 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > Actually, rereading SQL99, I wonder if it would expect this to work.
> > Using 14.1's wording on order by clauses, syntax rule 18, h
>
> Hmm ... that section is not exactly crystal-clear, is it? I had been
> thi
Well, that might help, thanks... :)
BTJ
On Mon, 2003-08-18 at 20:47, Steve Worsley wrote:
> fingerless=# select '7:43'::time AS start, '12:17'::time AS end,
> (('12:17'::time) - ('7:43'::time))::interval AS difference;
> start | end| difference
> --+--+
> 0
The osdl-dbt3 test starts with building and vacuuming the database.
The execution plans were taken after the vacuuming.
I did two tests with the same database parameters:
1. run two osdl-dbt3 runs on one box without rebooting the stystem.
Though the execution plans are the same, the costs are d
"Vilson farias" <[EMAIL PROTECTED]> writes:
> Let's suppose I can't use sequences (it seams impossible but my boss
> doesn't like specific database features like this one).
If sequences could be effectively replaced by standard SQL operations,
we would not have bothered to invent them. Nor woul
Is there a way to get an interval in a standard format? It seems like it
keeps changing it's ouput style based on the time length.
Jon
On Mon, 18 Aug 2003, Bruno Wolff III wrote:
> On Mon, Aug 18, 2003 at 16:09:43 +0200,
> Bjørn T Johansen <[EMAIL PROTECTED]> wrote:
> > I need to compute the
> wouldn't a better situation be ADDING a record that is one higher, and
> then doing a select MAX()?
>
> The different triggers could do delete on the old records.
>
In my case that would not apply, because what I had was a need to keep a
"sequence" counter for each employee, so I added a column
You can try
If you know root's password, you don't need set postgresql's password. But
if you don't known root password, you can't change password for postgres
user (for change of password you can use passwd command).
su root
su postgres
createuser aamehl
logout
logout
createdb nigun_test
b
Hi,
On 18 Aug 2003, Aaron wrote:
> [EMAIL PROTECTED] aamehl]# createdb -U postgres nigun_test
> psql: FATAL: IDENT authentication failed for user "postgres"
Quick and easy solution for you:
edit ~postgres/data/pg_hba.conf and replace all "ident" string to "trust".
Then, restart PostgreSQL se
Hervé Piedvache wrote:
An to be more precise what I exactly want to do :
select
case when 'now' between t.begin and t.end then t.login else 'None' end as log
from my_table t
order by lower(log);
Try:
select log from (select case when 'now' between t.begin and t.end then
t.login else 'None' end
On Mon, 18 Aug 2003, Tom Lane wrote:
> =?iso-8859-15?q?Herv=E9=20Piedvache?= <[EMAIL PROTECTED]> writes:
> > Is it an example more realistic for you to make an order by lower of
> > something as an alias ?
>
> Aliases attached to SELECT output columns are visible outside the
> SELECT, not inside i
- Original Message -
From: "Hervé Piedvache" <[EMAIL PROTECTED]>
To: "Darko Prenosil" <[EMAIL PROTECTED]>; "Postgresql General"
<[EMAIL PROTECTED]>
Sent: Monday, August 18, 2003 6:59 PM
Subject: Re: [GENERAL] Why lower's not accept an AS declaration ?
> Hi,
>
> An to be more precise what
On Monday 18 August 2003 18:59, Hervé Piedvache wrote:
> Hi,
>
> An to be more precise what I exactly want to do :
>
> select
> case when 'now' between t.begin and t.end then t.login else 'None' end as
> log from my_table t
> order by lower(log);
How about something like:
select
case when 'now'
On Monday 18 August 2003 01:41 pm, Aaron wrote:
> On Mon, 2003-08-18 at 21:25, Pavel Stehule wrote:
> > You can try
> >
> > If you know root's password,
>
> I just installed postgres from rpm and I didn't add passwords. I
> certainly don't know the postgres root password...
>
> maybe I should reins
- Original Message -
From: "Darko Prenosil" <[EMAIL PROTECTED]>
To: "Hervé Piedvache" <[EMAIL PROTECTED]>; "Postgresql General"
<[EMAIL PROTECTED]>
Sent: Monday, August 18, 2003 10:09 PM
Subject: Re: [GENERAL] Why lower's not accept an AS declaration ?
>
> - Original Message -
>
On Mon, Aug 18, 2003 at 14:57:12 -0400,
Dev <[EMAIL PROTECTED]> wrote:
> Hello all,
>
> I am working on setting up a table that will append a sequence to the end
> of the value inserted.
> Example;
> INSERT INTO test (test) VALUES ('abcd');
> And have the data in the database be;
> abcd0001
>
On Mon, 18 Aug 2003, Johann Uhrmann wrote:
> Hello,
>
> are there any experiences about the performance of indices
> with different data types.
>
> How do the performance of an index that consists of
>
> - an integer field
> - a varchar() field
> - a text field
>
> differ?
It's not so much ab
On Mon, 18 Aug 2003, Dev wrote:
> Hello all,
>
> I am working on setting up a table that will append a sequence to the end
> of the value inserted.
> Example;
> INSERT INTO test (test) VALUES ('abcd');
> And have the data in the database be;
> abcd0001
>
> Now I do have things setup else where wer
Stephan Szabo <[EMAIL PROTECTED]> writes:
> Actually, rereading SQL99, I wonder if it would expect this to work.
> Using 14.1's wording on order by clauses, syntax rule 18, h
Hmm ... that section is not exactly crystal-clear, is it? I had been
thinking of the part about deliberate incompatibiliti
At 02:57 PM 8/18/03 -0400, Dev wrote:
Hello all,
I am working on setting up a table that will append a sequence to the end
of the value inserted.
Example;
INSERT INTO test (test) VALUES ('abcd');
And have the data in the database be;
abcd0001
Now I do have things setup else where were the defaul
Hi Vilson,
Vilson farias wrote:
Does PostgreSQL sequences deal with these kinds performance questions? Is it
clean, fast and secury to use sequences? Maybe I still can make up my boss
mind about this subject if I get good arguments here to justify the use of
sequences.
Yes, exactly. Its clean, fa
On Mon, Aug 18, 2003 at 17:56:00 +0200,
Bjørn T Johansen <[EMAIL PROTECTED]> wrote:
> I am already using Time for time fields (i.e. timestamp fields without
> the date part) in my database, are you saying this doesn't work???
No. You can't use HHMM format for input without doing some more work.
On Mon, Aug 18, 2003 at 11:27:14 -0300,
Vilson farias <[EMAIL PROTECTED]> wrote:
>
> I tryied to fix this problem with a VACUUM and it was completly ineffective.
> After execution the problem was still there. Later, after execution of every
> kind of vacuum I knew (with and without ANALYZE, espe
Vilson farias wrote:
Greetings,
I'm getting a big performance problem and I would like to ask you what
would be the reason, but first I need to explain how it happens.
Let's suppose I can't use sequences (it seams impossible but my boss
doesn't like specific database features like this one).
Vilson farias wrote:
Does PostgreSQL sequences deal with these kinds performance questions? Is it
clean, fast and secury to use sequences? Maybe I still can make up my boss
mind about this subject if I get good arguments here to justify the use of
sequences.
Sorry, but you just outlined a sequence
On Mon, Aug 18, 2003 at 16:09:43 +0200,
Bjørn T Johansen <[EMAIL PROTECTED]> wrote:
> I need to compute the difference of Time fields, in the format HHMM. Is
> it possible to do the math in the Select?
Despite what it says in the documentation, you can't use that format
for the type time.
If tim
I would collect the way that 5-10 of the top databases handle unique id's (sequences) for columns and compare them in a small paper. Show your boss that sequences are fairly standard and he should come around.
Vilson farias wrote:
Greetings,
I'm getting a big performance problem and I would lik
I need to compute the difference of Time fields, in the format HHMM. Is
it possible to do the math in the Select?
Regards,
BTJ
--
---
Bjørn T Johansen (BSc,MNIF)
Executive Manager
[EMAIL PROTECTED]
On Mon, 2003-08-18 at 09:44, Martijn van Oosterhout wrote:
> It usually refers to some cached plan referring to a table or object that
> does not exist anymore. Do you have stored procedures that refer to tables
> that are deleted? This includes temporary tables.
>
> So maybe it's only happening w
On Fri, 8 Aug 2003 07:07:42 +0200 (CEST)
"Bjorn T Johansen" <[EMAIL PROTECTED]> wrote:
> I need to convert recordsets to XML, is there an automatic way to do this
> in PostgreSQL or a tool I can use? Or do I have to code this manually?
Agata Report (agata.codigolivre.org.br) does that.
Pablo
>
Hi Jan/Sean
To the list I bow and apologise for wasting your time! I did not
appropriately test the function (as is my want!) and passed command line
arguments in the executable image path in the execl function. Now I edit my
table containing the configuration vars for the trackformat to deco
It usually refers to some cached plan referring to a table or object that
does not exist anymore. Do you have stored procedures that refer to tables
that are deleted? This includes temporary tables.
So maybe it's only happening when a certain stored procedure is executed
twice in the same session?
Greetings,
I'm getting a big performance problem and I would like to ask you what
would be the reason, but first I need to explain how it happens.
Let's suppose I can't use sequences (it seams impossible but my boss
doesn't like specific database features like this one).
For sequence simul
On Mon, 2003-08-18 at 09:53, Tom Lane wrote:
> Always the same OID, or different ones? Does that OID actually exist in
> pg_class? Can you tell us exactly what SQL command(s) are producing the
> error? (If not, better turn on query logging so you can find out.)
Different OIDs, and they do not
"Jeff Boes" <[EMAIL PROTECTED]> writes:
> What might be the source of this error?
> Cache lookup failed for relation 188485009
> We've been getting these at odd intervals, and they are not reproducible.
Always the same OID, or different ones? Does that OID actually exist in
pg_class? Can you te
On Sat, 2003-08-16 at 15:12, Jochem van Dieten wrote:
> Glen Eustace wrote:
> >
> > We recently purchased Dreamweaver MX and I was a little surprised to
> > find that one of its pre-defined scripting systems is PHP + MySQL. I
> > haven't done much exploring of what is actually offered but wondered
You forgot the: -L /usr/local/pgsql/lib -lpq
Hope this helps,
On Mon, Aug 18, 2003 at 02:01:08PM +0200, Marc Cuypers wrote:
> Hi,
>
> How do i make the examples in postgresql-7.3.3/src/test/examples/?
> It seems that just typing make doesn't link to the libpq library. Where
> should I start ma
Tom Lane wrote:
Marc Cuypers <[EMAIL PROTECTED]> writes:
How do i make the examples in postgresql-7.3.3/src/test/examples/?
"make" works for me, assuming that I'm doing it in a built directory
tree.
[ looks at 7.3 branch... ] Hm, it looks like there's a mistake in the
Makefile in that director
Marc Cuypers <[EMAIL PROTECTED]> writes:
> How do i make the examples in postgresql-7.3.3/src/test/examples/?
"make" works for me, assuming that I'm doing it in a built directory
tree.
[ looks at 7.3 branch... ] Hm, it looks like there's a mistake in the
Makefile in that directory in 7.3: try ch
Jason Godden wrote:
Hi Sean,
Yeah - It is declared VOLATILE. I think there must be something specific with
the way PL/PGSQL handles child processes of a called function. The child
process actually spawns mpg123 or ogg123 so it has to live beyond the life of
the parent. Not sure. What I migh
Deepa K <[EMAIL PROTECTED]> writes:
> I am using postgresql 7.1.3 and a client using libpq.
> I am executing a statement with muliple SQL commands semicolon
> seperated.
> If any one of the query in between fails, pqGetResultset returns
> NULL on the failed query. So i am unable to pro
What might be the source of this error?
Cache lookup failed for relation 188485009
We've been getting these at odd intervals, and they are not reproducible.
Our setup:
PostgreSQL 7.3.3
Red Hat 7.3
kernel.shmall = 1352914698
kernel.shmmax = 1352914698
shared_buffers = 131072
max_fsm_pag
Hi,
How do i make the examples in postgresql-7.3.3/src/test/examples/?
It seems that just typing make doesn't link to the libpq library. Where
should I start make?
Just typing make gives the following output:
# make
gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations
-I../../../src/inter
On Monday 18 August 2003 13:04, Darko Prenosil wrote:
> On Monday 18 August 2003 10:20, Hervé Piedvache wrote:
> > Hi,
> >
> > May be my question is stupid ... but I'm a little suprised :
> >
> > SELECT id_letter as letter from my_table;
> >
> > letter
> > -
> > B
> > C
> > a
> > A
> >
> > SELE
On Monday 18 August 2003 10:20, Hervé Piedvache wrote:
> Hi,
>
> May be my question is stupid ... but I'm a little suprised :
>
> SELECT id_letter as letter from my_table;
>
> letter
> -
> B
> C
> a
> A
>
> SELECT id_letter as letter from my_table order by letter;
>
> letter
> -
> A
> B
> C
On Mon, Aug 18, 2003 at 10:20:13AM +0200, Hervé Piedvache wrote:
> You can imagine my test is simple, in practise it's not the reallity of my
> original request ... but this example is just to show that lower() function
> does not accept an AS declaration ... is it normal ?
Yes, that's normal. I
Hi Sean,
Yeah - It is declared VOLATILE. I think there must be something specific with
the way PL/PGSQL handles child processes of a called function. The child
process actually spawns mpg123 or ogg123 so it has to live beyond the life of
the parent. Not sure. What I might do is rewrite the
Hi, I am having problems manipulating bit strings.
CREATE TABLE lookup(
fname TEXT PRIMARY KEY,
digest BIT VARYING
);
I am trying to construct another bit string based on the length of the
first:
SELECT b'1'::bit( bit_length( digest ) ) FROM lookup;
This doesn't work as i had hoped, where am I
Hello,
are there any experiences about the performance of indices
with different data types.
How do the performance of an index that consists of
- an integer field
- a varchar() field
- a text field
differ?
Is it a waste of memory/performance to make a text field
primary key?
Thanks,
Hans
Hi,
May be my question is stupid ... but I'm a little suprised :
SELECT id_letter as letter from my_table;
letter
-
B
C
a
A
SELECT id_letter as letter from my_table order by letter;
letter
-
A
B
C
a
SELECT id_letter as letter from my_table order by lower(letter);
ERROR: Attribute "l
63 matches
Mail list logo