[SQL] Experience with splitting a Table transparently

[KÖPFERL Robert]

Hi,

I'm looking for people who have got experience at splitting a table with
heavy records into two relations.

In my case there exists one table with the mentioned heavy records. These
are processed by a statemachine. Thus a bunch of columns gets changed
several times in the livetime of one record. The text and blob colmns remain
unchanged. However due to Postgres# MVCC one changed record gets duplicated,
even if just one bit is changed. This makes us IO-performance problems.

I intend now to split the table in transport data (which stays constant) and
status data (which is changed several times).

I have then several options: Change all stored procedures to mathc the new
relations and keep the interface equal.
Or create a view with a bunch of RULES to show the procedures the 'original
table'. 
What is best?
Who has done such bevore?
Will this help us in IO-terms?



thanks

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Cursor need it?

[Lucas Hernndez]

I am a new postgres user

I want to get a list of tables from pg_tables where tables are like 
‘%wo%’ (for example).. and then query that list ….


Select count(*) from tableVARIABLENAMEFROMFIRSTQUERY

In SQL SERVER I can do that using cursor but in postgresql I don’t 
understand how to use cursors


Here is what I am trying to do
for each table on the list
Select tablename, count(*) tables from ( list of tables)

tablename tables
table1 25
table2 35

Any Idea would be appreciated

Thanks

Lucas


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] How do write a query...

[Bruno Wolff III]

Please keep replies copied to the list unless you have a specific reason not
to. This will help you get help and will help other people learn from
the discussion.

On Wed, Jun 08, 2005 at 13:56:36 +0200,
  Alain <[EMAIL PROTECTED]> wrote:
> 
> In fact, these are results of analyses. For one patient and one type of
> meseare, I can have results at different dates and I need to follow the
> differences between dates. So, if I have 5 records for one person, I'll
> have 4 values of differences (record2-rec1, rec3-rec2, rec4-rec3,
> rec5-rec4). Problem is to create the pairs from the records I have. I
> can do it with an algorithm. But a query - if possible - returning the
> number of days and the differences between the values would be nicer.

This is actually easier. You can write a subquery that gets the row that
has the greatest date less than the current date. With an index on Num
and the date column it shouldn't be too slow.

Below is an example test script and the output. This relies on Postgres
supprting ORDER BY in subselects which isn't standard SQL. You can do this
with standard SQL but that will require getting the max value of day less
than the day in the current record with a matching num and then joining
that result back to test to get the corresponding value.

DROP TABLE test;
CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  num INT NOT NULL,
  day DATE NOT NULL,
  value INT NOT NULL,
  UNIQUE (num, day)
);

INSERT INTO test (num, day, value) VALUES (10, '2005-01-01', 50);
INSERT INTO test (num, day, value) VALUES (10, '2005-05-31', 60);
INSERT INTO test (num, day, value) VALUES (25, '2005-02-02', 55);
INSERT INTO test (num, day, value) VALUES (25, '2005-03-15', 43);
INSERT INTO test (num, day, value) VALUES (25, '2005-05-28', 62);

SELECT num, day, value,
  value - (SELECT value FROM test
 WHERE
   num = a.num
   AND
   day < a.day
 ORDER BY num DESC, day DESC
 LIMIT 1)
  FROM test a
  WHERE
   (SELECT value FROM test
 WHERE
   num = a.num
   AND
   day < a.day
 ORDER BY num DESC, day DESC
 LIMIT 1)
   IS NOT NULL
  ORDER BY num, day
;

bruno=> \i test.sql
DROP TABLE
psql:test.sql:8: NOTICE:  CREATE TABLE will create implicit sequence 
"test_id_seq" for serial column "test.id"
psql:test.sql:8: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"test_pkey" for table "test"
psql:test.sql:8: NOTICE:  CREATE TABLE / UNIQUE will create implicit index 
"test_num_key" for table "test"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 num |day | value | ?column?
-++---+--
  10 | 2005-05-31 |60 |   10
  25 | 2005-03-15 |43 |  -12
  25 | 2005-05-28 |62 |   19
(3 rows)

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Cursor need it?

[KÖPFERL Robert]

|
|I am a new postgres user
|
|I want to get a list of tables from pg_tables where tables are like 
|‘%wo%’ (for example).. and then query that list ….
|
|Select count(*) from tableVARIABLENAMEFROMFIRSTQUERY
|
|In SQL SERVER I can do that using cursor but in postgresql I don’t 
|understand how to use cursors

Sorry, but this is a SQL-Server. 
You are looking for cross table joins. Join the pg_class table (where you
get the table name) with another table which you want to query additional
data. Then select your columns off both tables.

select p.name, m.foo from pg_class p JOIN myinfo m ON p.name=m.info 
this is equal to
select p.name, m.foo from pg_class p, myinfo m  where p.name=m.info 

|
|Here is what I am trying to do
|for each table on the list
|Select tablename, count(*) tables from ( list of tables)
|
|tablename tables
|table1 25
|table2 35
|
|Any Idea would be appreciated
|
|Thanks
|
|Lucas
|
|
|---(end of 
|broadcast)---
|TIP 2: you can get off all lists at once with the unregister command
|(send "unregister YourEmailAddressHere" to 
|[EMAIL PROTECTED])
|

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] access to new/old in dynamic sql for audit table

[Jay Parker]

I am revisiting the age-old audit table issue, and am curious to see 
whether I can get away with not writing custom trigger functions for 
every table being audited.


My design has a foo_audit schema for each schema foo containing tables 
to be audited, so triggers on foo.bar will insert rows into 
foo_audit.bar, which is a clone of foo.bar with some extra columns added.


I would like to have three generic functions for insert/update/delete, 
instead of three custom functions for each audited table.  The problem, 
of course, is handling the different column structures.  I suppose I 
could go look things up in the catalog and generate dynamic sql based on 
the tables structure recorded there, but that seems like way too much 
overhead for an audit system.


I tried something like this, but it didn't like the reference to "new" 
inside the execute string:


CREATE OR REPLACE FUNCTION
  meta.audit_insert_trig() RETURNS trigger SECURITY DEFINER
AS $PROC$
DECLARE
   varschema TEXT;
BEGIN
  varschema = tg_argv[0];
  if varschema is null or char_length(varschema) = 0 then
raise exception 'must create trigger with schema name as arg';
  end if;
  new.auditrowid := nextval('meta.auditrowid_seq');
  execute 'insert into '
|| quote_ident(varschema||'_audit.'||tg_relname)
|| ' select now(),NULL,''I'',new.*';
  return new;
END
$PROC$ LANGUAGE plpgsql;

The error I get is: "ERROR:  NEW used in query that is not in a rule", 
which doesn't seem like an unreasonable limitation.  Is there any way to 
accomplish this (efficiently) in a generic function, or should I go back 
to my previous implementation with dozens of custom trigger functions?


While I've got your attention, I'll toss in some related questions:

- What is the efficiency tradeoff of having a single combined function 
with a conditional to detect insert/update/delete, versus having three 
specific functions that duplicate some common code but don't have the 
conditional?


- Is there an easier way to get the name of the schema associated with a 
table from inside a trigger, rather than the trigger argument kludge I 
used above?


Thanks,

-jbp

--
Jay Parker  -  UALR Computing Services  -  Networks Project Manager
[EMAIL PROTECTED]  -  http://www.ualr.edu/jbparker  -  501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] access to new/old in dynamic sql for audit table

[Tom Lane]

Jay Parker <[EMAIL PROTECTED]> writes:
> I am revisiting the age-old audit table issue, and am curious to see 
> whether I can get away with not writing custom trigger functions for 
> every table being audited.

You can't do it nohow in plpgsql.  I believe it's relatively simple in
pltcl or plperl, though, because those languages don't try to precompile
column references.

Or if you're feeling bold, you could write the trigger in C ;-).
There are some examples that do pretty similar stuff in contrib/spi/.
If performance is a big concern this would be a good plan anyway.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] plpgsql dynamic record access

[Rajesh Kumar Mallah]

Ganesh,

Did you have a look at example Example 35-2. A PL/pgSQL
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html

Regds
maLLAH

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Rule

[David Hofmann]

I have a table with 3 fields,  id, s_data, and time_stamp.  The time_stamp 
field is set to now() by deault.


The program that uses this table only uses the id and s_data file. I added 
and use the time_stamp field to delete old records after a certain time.


What I want to do is setup some kind of rule so that whenever a s_data field 
is updated, that the time_stamp gets update to the current time/date. The 
program regretab


I'm not really familar with rules, I've only used them in a certain places 
and very limitedly.


Any help would be greatly appercated.

David Hofmann

_
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] Rule

[Bruno Wolff III]

On Wed, Jun 08, 2005 at 15:51:35 -0400,
  David Hofmann <[EMAIL PROTECTED]> wrote:
> 
> What I want to do is setup some kind of rule so that whenever a s_data 
> field is updated, that the time_stamp gets update to the current time/date. 

Normally you want to do that with a before trigger rather than a rule.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Rule

[David Hofmann]

Ok, I have no knowledge of Tiggers except what I just read in the docs 
section. Look like I need to make a procudure then call it with a trigger.


Is there a better location for Tigger/Procudure Examples. The trigger seems 
fairly, however I got lost in the procudure part.


David



Normally you want to do that with a before trigger rather than a rule.


_
FREE pop-up blocking with the new MSN Toolbar – get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Rule

[Keith Worthington]

David wrote:

What I want to do is setup some kind of rule so that whenever a s_data 
field is updated, that the time_stamp gets update to the current time/date. 


Normally you want to do that with a before trigger rather than a rule.


Ok, I have no knowledge of Tiggers except what I just read in the docs 
section. Look like I need to make a procudure then call it with a trigger.


Is there a better location for Tigger/Procudure Examples. The trigger 
seems fairly, however I got lost in the procudure part.


David


David,

Here is a trigger function that I wrote for storing audit information. 
Whether or not a query provides the user and/or timestamp this procedure 
sets them.  Naturally you will need to modify them for your data model.


CREATE OR REPLACE FUNCTION interface.tf_audit_data()
  RETURNS "trigger" AS
$BODY$
   BEGIN
--Set the user name.
  SELECT * FROM session_user INTO NEW.audit_user;
--Set the timestamp.
  NEW.audit_timestamp := ('now'::text)::timestamp(6) with time zone;
--Send the modified record down the pipe.
  RETURN NEW;
   END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION interface.tf_audit_data() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO public;

CREATE TRIGGER tgr_audit_data
  BEFORE INSERT OR UPDATE
  ON sales_order.tbl_line_item
  FOR EACH ROW
  EXECUTE PROCEDURE interface.tf_audit_data();

--
Kind Regards,
Keith

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] SELECT very slow

[Thomas Kellerer]

Hello,

I have a table with roughly 100,000 rows (four varchar(100) columns). This 
is basically test data I generated for something else. I'm using JDBC to 
access PG (but the behaviour is the same with psql).


The problem is, that a SELECT * FROM foobar; takes ages (roughly 3 minutes) 
to return the first row. I played around with the fetchSize() to disable 
the result set caching in the Java program first (before I tried psql) but 
that did not change anything.


It seems that PG is actually building up the full result set in the 
background before delivering the first row. But I cannot see any of the 
processes (neither my Java process nor the PG processes) using a lot of 
memory - which I would expect if a result set of that size is created.


I need to test a program which should process large result sets (without 
loading it into memory) and waiting nearly three minutes before it actually 
starts working is a bit annoying :)

A SELECT count(*) FROM foobar is quite fast (about 2 seconds)

I hate to say this, but the same SELECT returns the first row more or less 
instantly with Firebird, SQL Server (MSDE) and HSQLDB.


Is there anything I can do, to convince PG to return the first row more 
quickly?


I tried a VACUUM FULL, no change. I increased the shared_buffers to 1000, 
no improvement either.


The execution plan is not really surprising:

Seq Scan on foobar  (cost=0.00..2510.04 rows=117504 width=63)


I'm using PG 8.0.3 on Win2K.

Thanks in advance
Thomas


---(end of broadcast)---
TIP 8: explain analyze is your friend

[Fwd: RE: Re: [SQL] Rule]

[Keith Worthington]

Personally I feel that if this individual can't be bothered to white 
list the postgresql.org domain they should be banned from the list.


Kind Regards,
Keith

 Original Message 
Subject:RE: Re: [SQL] Rule
Date:   Wed, 8 Jun 2005 19:02:39 -0300 (BRT)
From:   AntiSpam UOL <[EMAIL PROTECTED]>
To: keithw <[EMAIL PROTECTED]>

ANTISPAM UOL » TIRA-TEIMA 

Olá,

Você enviou uma mensagem para [EMAIL PROTECTED]
Para que sua mensagem seja encaminhada, por favor, *clique aqui*



Esta confirmação é necessária porque [EMAIL PROTECTED] usa o
Antispam UOL, um programa que elimina mensagens enviadas por robôs, como
pornografia, propaganda e correntes.

*As próximas mensagens enviadas para [EMAIL PROTECTED] não
precisarão ser confirmadas*.*
*Caso você receba outro pedido de confirmação, por favor, peça para
[EMAIL PROTECTED] incluí-lo em sua lista de autorizados.

*Atenção!* Se você não conseguir clicar no atalho acima, acesse este
endereço:
http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVAMqAqRQr%2BDFbu2DcFQ10C0ySypbrCRoslvleIrQFAMO7c36mP3ZUehXUEg0dUgva%2BhU%0AWwUDyU5D032tO3eNaWIJ%2BSThmoun81A9a3mbOEklX9tn%2FC6RHBh4



Hi,

You´ve just sent a message to [EMAIL PROTECTED]
In order to confirm the sent message, please *click here*



This confirmation is necessary because [EMAIL PROTECTED] uses
Antispam UOL, a service that avoids unwanted messages like advertising,
pornography, viruses, and spams.

*Other messages sent to [EMAIL PROTECTED] won't need to be confirmed*.*
*If you receive another confirmation request, please ask
[EMAIL PROTECTED] to include you in his/her authorized e-mail list.

*Warning!* If the link doesn´t work, please copy the address below and
paste it on your browser:
http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVAMqAqRQr%2BDFbu2DcFQ10C0ySypbrCRoslvleIrQFAMO7c36mP3ZUehXUEg0dUgva%2BhU%0AWwUDyU5D032tO3eNaWIJ%2BSThmoun81A9a3mbOEklX9tn%2FC6RHBh4


Use o *AntiSpam UOL*  e proteja sua caixa postal



Personally I feel that if this individual can't be bothered to white 
list the postgresql.org domain they should be banned from the list.


--
Kind Regards,
Keith

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] SELECT very slow

[Tom Lane]

Thomas Kellerer <[EMAIL PROTECTED]> writes:
> Is there anything I can do, to convince PG to return the first row more 
> quickly?

libpq's API for PQresult is such that it really doesn't have any choice
but to collect the full result set before it hands you back the
PQresult.  I don't know JDBC very well but I think it has similar
semantic constraints.

The solution is to use a cursor and FETCH a reasonably small number of
rows at a time.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] SELECT very slow

[Alain]

Tom Lane escreveu:

Thomas Kellerer <[EMAIL PROTECTED]> writes:

Is there anything I can do, to convince PG to return the first row more 
quickly?


Are you now looking for the LIMIT ?

SELECT * FROM table LIMIT 1;

and when when you wnat the rest of it:

SELECT * FROM table OFFSET 1;

Alain

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Indices and user defined operators

[Dmitri Bichko]

Being lazy, I've created a set of case incensitive text comparison
operators: =*, <*, >*, and !=*; the function for each just does an
UPPER() on both arguments and then uses the corresponding builtin
operator.

What would make these REALLY useful, is if when running something like:

SELECT * FROM foo WHERE bar =* 'baz'; 

postgres would know to use an index defined as:

CREATE INDEX idx_foo_bar ON foo (UPPER(bar));

Currently, the explain I get for the above situation is:

Seq Scan on foo  (cost=0.00..8696.81 rows=1324 width=34)
   Filter: (upper(upper((bar)::text)) = 'BAZ'::text)

I am vaguely aware of the concept of operator classes, but from what I
understand, the op class has to be specified at index creation time;
seeing how this is just to save a little typing, I'd rather not have to
rely on these ops and opclasses being defined for the rest of the
database to work.  Also I need to still be able to do SELECT * FROM foo
WHERE UPPER(bar) = UPPER('baz') and have it use the same index.

So, is there any way to make these operators use an index defined as
above?

Thanks,
Dmitri


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Indices and user defined operators

[Tom Lane]

"Dmitri Bichko" <[EMAIL PROTECTED]> writes:
> So, is there any way to make these operators use an index defined as
> above?

If you've set things up so that the operators are defined by inline-able
SQL functions, I'd sort of expect it to fall out for free ...

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Indices and user defined operators

[Tom Lane]

I wrote:
> "Dmitri Bichko" <[EMAIL PROTECTED]> writes:
>> So, is there any way to make these operators use an index defined as
>> above?

> If you've set things up so that the operators are defined by inline-able
> SQL functions, I'd sort of expect it to fall out for free ...

Here's a quick proof-of-concept:

regression=# create function iequal(text,text) returns bool as
regression-# 'select upper($1) = upper($2)' language sql strict immutable;
CREATE FUNCTION
regression=# create operator *= (procedure = iequal, leftarg = text,
regression(# rightarg = text , commutator = *= );
CREATE OPERATOR
regression=# explain select * from text_tbl where f1 *= 'foo';
   QUERY PLAN
-
 Seq Scan on text_tbl  (cost=0.00..1.03 rows=1 width=32)
   Filter: (upper(f1) = 'FOO'::text)
(2 rows)

regression=# create index fooi on text_tbl(upper(f1));
CREATE INDEX
regression=# set enable_seqscan TO 0;  -- because my test table is tiny
SET
regression=# explain select * from text_tbl where f1 *= 'foo';
  QUERY PLAN  
--
 Index Scan using fooi on text_tbl  (cost=0.00..4.68 rows=1 width=32)
   Index Cond: (upper(f1) = 'FOO'::text)
(2 rows)

This is with CVS tip, but I'm pretty sure it works as far back as 7.4.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq