Martijn van Oosterhout wrote:
On Sun, Aug 17, 2003 at 02:04:03PM -0600, Scott Lamb wrote:
Martijn van Oosterhout wrote:
grant select on :2 to :1
\for select usename, relname from pg_catalog.pg_user, pg_catalog.pg_class
where relname not like 'pg_%';
That's definitely a useful thing to do, but
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
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, 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
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
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.
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
a
SELECT
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
SELECT id_letter as letter from
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
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
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 process
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
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 directory
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 make?
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
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 tell us
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
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
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
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
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
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 when
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]
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
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
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
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).
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, especific
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.
You
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,
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
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 incompatibilities
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 were the
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 about the
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
Now I do
- 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 -
From: Hervé
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 reinstall
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'
- 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 I exactly
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 it. The
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'
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 server.
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
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
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
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 would
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
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
--+--+
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
thinking of
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
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
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
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 cases
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
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.
Yes, it
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 always
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(*) from
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
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) from table
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
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
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
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
63 matches
Mail list logo
