"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> It turns out that even when I removed the CASE statement from the VIEW, the
> performance problem remained. I had to remove the conditional as well from
> the VIEW.
> cancel_date < expire_date AS canceled,
Yuck :-(
> Do you know w
Sergio Fantinel wrote:
I found how to use, inside a PL/pgSQL function, a two-dimensions array
(matrix).
There is a limitation: the number of the 'columns' of the matrix is
fixed at declaration time (in DECLARE section) and you need to manually
initialize all the elements in the first 'row' of th
It turns out that even when I removed the CASE statement from the VIEW, the
performance problem remained. I had to remove the conditional as well from
the VIEW.
To refresh your memory, there was this line in the VIEW (said VIEW being
the subject of the LEFT JOIN):
cancel_date < expire_date AS
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> I could ask why a CASE statement is always non-nullable, but I don't think
> the answer would help be solve my problem. I suppose it's that even
> though my particular CASE statement has WHEN/ELSE values that come from the
> nullab
On Mon, 20 Sep 2004, Dean Gibson (DB Administrator) wrote:
> Okay, now for my big question: I searched high and low for a function that
> would return the minimum of two dates, and found none. Now you come up
> with "date_smaller", which works fine (as does "date_larger"), but where
> are those d
Ahh, that explains why a "plain" JOIN (which doesn't do what I need) gave
much better performance than the LEFT JOIN.
I could ask why a CASE statement is always non-nullable, but I don't think
the answer would help be solve my problem. I suppose it's that even
though my particular CASE statem
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> Question: why do the last two column definitions in the second VIEW change
> the scan on _LicHD from indexed to sequential ??
It's the CASE that's getting you. The poor plan is basically because
the sub-view isn't getting "flattened
Tom Lane wrote on 2004-09-20 16:06:
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> I have a view that when used, is slow:
... If you want useful help you need to be more complete.
I use views to "hide" tables so that I can populate new tables and then
atomically switch to them with
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
> I have a view that when used, is slow:
It's obvious that you haven't given us anything remotely like full
information here. AFAICT the "tables" in your view are actually
other views, plus it looks like your call to the view is a query
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Arash Zaryoun wrote:
| Hi All,
|
| I have a table which has 'SERIAL' datatype. When I use 'create table X
| as select * from my-table' , it creates the table but without serial
| datatype. I mean without implicit sequence.
|
| test=> \d qptuser
|
Mensagem Enviada utilizando o Onda Mail.
http://www.onda.com.br
Onda Provedor de Servicos S/A
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http
On Mon, 20 Sep 2004, T E Schmitz wrote:
> I was feeling a bit guilty about posting such a trivial question. I can
> cobble together some straightforward SQL but I could really do with a
> source of more complex SQL examples.
> If you know of any links - that would great and save the list from more
On Mon, 20 Sep 2004, CHRIS HOOVER wrote:
> I need some help writing a simple function.
>
> Due to some program limitations for a program I run the db's for, I'm having
> to write some simple functions to run some selects. However, I am not sure
> how to have them correctly return the record(s) se
T E Schmitz <[EMAIL PROTECTED]> writes:
> ) as somealias on (model_fk = model_pk)
>
> (subquery in FROM must have an alias)
ARGH! This is one of the most annoying things about postgres! It bites me all
the time. Obviously it's totally insignificant since it's easy for my to just
throw an "AS x"
I need some help writing a simple function.
Due to some program limitations for a program I run the db's for, I'm having
to write some simple functions to run some selects. However, I am not sure
how to have them correctly return the record(s) selected and/or how to
properly call them from sql.
Hello Greg,
You have given me plenty of food for thought. Thank you for taking the
time.
Currently, the tables have such few records (350, 900, 1000) that
performance does not come into it, particularly seeing as this was only
needed for a one-shot report.
However, I have stached your examples a
I have a view that when used, is slow:
CREATE VIEW "Data".genlic_a4avail AS
SELECT
genlic_a4.*,
last_action_date,
end_date,
canceled
FROMgenlic_a4
LEFT JOIN lic_hd USING( sys_id )
T E Schmitz <[EMAIL PROTECTED]> writes:
> SELECT
> BRAND_NAME,MODEL_NAME
...
> intersect
...
Huh, I never think of the set operation solutions. I'm curious how it compares
speed-wise.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our
T E Schmitz <[EMAIL PROTECTED]> writes:
> I want to select only those BRAND/MODEL combinations, where the MODEL has more
> than one TYPE, but only where one of those has TYPE_NAME='xyz'.
> I am not interested in MODELs with multiple TYPEs where none of them are called
> 'xyz'.
There are lots of
Hi Rod,
Rod Taylor wrote:
On Mon, 2004-09-20 at 12:19, T E Schmitz wrote:
I figured it eventually. (The only thing I don't know is where to put
the ORDER BY.)
Try this:
SELECT brand_name, model_name
FROM (SELECT ... INTERSECT SELECT ...) AS t
ORDER BY ...
That do
On Mon, 2004-09-20 at 12:19, T E Schmitz wrote:
> I figured it eventually. (The only thing I don't know is where to put
> the ORDER BY.)
Try this:
SELECT brand_name, model_name
FROM (SELECT ... INTERSECT SELECT ...) AS t
ORDER BY ...
---
Richard,
> But only if 'sometable.csv' exists;
>
> If 'sometable.csv' does not exist as an input table I want to continue
> the next command.
Sorry, can't be done in plpgsql. Regular procedural languages are
deliberately kept ignorant of the host filesystem in order to prevent
database users
I figured it eventually. (The only thing I don't know is where to put
the ORDER BY.)
I want to select only those BRAND/MODEL combinations, where the MODEL
has more than one TYPE, but only where one of those has TYPE_NAME='xyz'.
I am not interested in MODELs with multiple TYPEs where none of them a
Hello,
I apologize in advance for this garbled message but I've been banging my
head against a brick-wall for a while and I just can't figure how to do
the following:
I have 3 tables BRAND,MODEL,TYPE which are related to each other:
BRAND
=
BRAND_PK
BRAND_NAME
MODEL
=
MODEL_PK
MODEL_NAME
Funny... I set up a little program on my development
machine that queries for the rendezvous name. This way
I know my development machine is running before I try
to connect.
I think zeroconf could be used for this quite
easily...
JMHO
Ted
--- Arne Stoelck <[EMAIL PROTECTED]> wrote:
>
> How a
Chip Gobs wrote:
>
> We are porting from Informix to PostgreSQL 7.4.5 and have noticed the
> following behavior.
>
> If we try to OPEN a CURSOR for an invalid SELECT statement in ECPG, we
> get an error, as expected. However, if we then
> attempt to OPEN another CURSOR for a valid statement,
Tom Lane wrote:
> At one time there was discussion of writing a "pg_ping" utility program
> to do exactly this, but it still hasn't got done. You can fake it to
> some extent by just running "psql -l >/dev/null" and checking the exit
> code, but this does require supplying a valid username and pos
Am Montag, 20. September 2004 12:05 schrieb Gaetano Mendola:
> It only depends on your distribution, in your case:
>
> ps aux | grep /usr/lib/postgresql/bin/postmaster | grep -v grep
>
> consider also the if you run different postmaster version in different
> location this is the only way I believe
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Worik wrote:
|
|
| [snip]
|
|>
|>
|> Just to enforce the test is better looking for the entire executable
|> path:
|>
|> ps aux | grep /usr/bin/postmaster | grep -v grep
|>
|
| Does not work for me!
|
| [EMAIL PROTECTED]:~$ ps aux | grep /usr/bi
Hello,
Greg Stark wrote:
Tom Lane <[EMAIL PROTECTED]> writes:
The paragraph continues:
"If the SELECT command included the clause WHERE phone NOT NULL,
PostgreSQL could use the index to satisfy the ORDER BY clause.
An index that covers optional (NOT NULL) columns will not be used to
speed table
Hello!
Our check procedure on TRU64 UNIX is:
checkmasterdaemon
if [ $? -eq 1 ]; then
# Try to connect to postgres by selecting tables
TABELLEN=`/pg/postgresql-7.4.3/bin/psql -h postgres -c 'select datname from
pg_database' postgres postgres 2>/dev/null`
if [[ "X$TABEL
31 matches
Mail list logo
