[SQL] database design and diagraming book recommendations..
anyone know of a good book or books on database modeling? like for entity relationship diagrams and such.. thanks!
Re: [SQL] Continuous inserts...
Hi! Thanks all for your input... At 09:15 17.08.00 -0700, you wrote: [...] > > Question: would it work to use a transaction to perform the rename? > > > > i.e.: continuous insert into table 'main' from client. > > > > From somewhere else, execute: > > > > begin; > > alter table main rename to vac_main; > > create table main (...); > > end; > > > > would the inserter notice this? Read: would ALL inserts AT ANY TIME > succeed? > >Unfortunately -- no. Also, bad things can happen if the transaction >errors since the rename happens immediately. There's been talk on >-hackers about this subject in the past. > >However, you might be able to do something like this, but >I'm not sure it'll work and it's rather wierd: > >Have three tables you work with, a and b and c > >Set up rule on a to change insert to insert on b. >Insert into a. >When you want to vacuum, change the rule to insert to c. >Vacuum b >Change rule back >move rows from a and c into b >vacuum c [...] Good idea - I immediately tested it - rules rule! That seems to work perfectly, and the client doesn't even see it happen (except for 'selects', one would have to setup a rule to return something meaningful then...). I did: Two tables, a and b. Normally, insert into a. When Vacuuming starts, create rule on a to insert into b Vacuum a drop rule copy records from b to a vacuum b Why did you suppose three tables? Did I overlook something? Greetings, Joerg +-- Science & Engineering Applications GmbH --+ | | | Joerg Hessdoerfer | | Leading SW developer Phone:+49 (0)2203-962211 | | S.E.A GmbH Fax: -962212 | | D-51147 KoelnInternet: [EMAIL PROTECTED] | |http://www.sea-gmbh.com| +---+
Re: [SQL] Continuous inserts...
hi! At 11:38 17.08.00 -0700, you wrote: >Hi All. > >Shouldn't Postgres block while vacuuming, and then >continue inserting starting where it left off? Is the >time lag too much? For me - yes. My app can accept some hundredes of ms time lag - not seconds or, like with a VACUUM, minutes (I've seen it taking hours, but that was a _LARGE_ table on 6.5.x). >I am curious because I am going to build a similar app >soon, basically parsing and inserting log file >entries. Rules do the trick - insert into a second table unnoticed by the inserter client. See my previous posting for slightly more detail. Greetings, Joerg +-- Science & Engineering Applications GmbH --+ | | | Joerg Hessdoerfer | | Leading SW developer Phone:+49 (0)2203-962211 | | S.E.A GmbH Fax: -962212 | | D-51147 KoelnInternet: [EMAIL PROTECTED] | |http://www.sea-gmbh.com| +---+
Re: [SQL] Continuous inserts...
I'm not familiar with rules. Could you please post the SQL for creating the rule that you've created? I going to make such a setup in the near future and this seems to . Joerg Hessdoerfer wrote: > Hi! > > Thanks all for your input... > > At 09:15 17.08.00 -0700, you wrote: > [...] > > > Question: would it work to use a transaction to perform the rename? > > > > > > i.e.: continuous insert into table 'main' from client. > > > > > > From somewhere else, execute: > > > > > > begin; > > > alter table main rename to vac_main; > > > create table main (...); > > > end; > > > > > > would the inserter notice this? Read: would ALL inserts AT ANY TIME > > succeed? > > > >Unfortunately -- no. Also, bad things can happen if the transaction > >errors since the rename happens immediately. There's been talk on > >-hackers about this subject in the past. > > > >However, you might be able to do something like this, but > >I'm not sure it'll work and it's rather wierd: > > > >Have three tables you work with, a and b and c > > > >Set up rule on a to change insert to insert on b. > >Insert into a. > >When you want to vacuum, change the rule to insert to c. > >Vacuum b > >Change rule back > >move rows from a and c into b > >vacuum c > [...] > > Good idea - I immediately tested it - rules rule! That seems to work perfectly, > and the client doesn't even see it happen (except for 'selects', one would > have to setup > a rule to return something meaningful then...). > > I did: > Two tables, a and b. > Normally, insert into a. > When Vacuuming starts, create rule on a to insert into b > Vacuum a > drop rule > copy records from b to a > vacuum b > > Why did you suppose three tables? Did I overlook something? > > Greetings, > Joerg > +-- Science & Engineering Applications GmbH --+ > | | > | Joerg Hessdoerfer | > | Leading SW developer Phone:+49 (0)2203-962211 | > | S.E.A GmbH Fax: -962212 | > | D-51147 KoelnInternet: [EMAIL PROTECTED] | > |http://www.sea-gmbh.com| > +---+
Re: [SQL] Continuous inserts...
Hi! At 11:57 18.08.00 +0100, you wrote: >I'm not familiar with rules. Could you please post the SQL for creating >the rule >that you've created? Here we go (if memory serves ;-) create table a ( num int4, name text ); create table b ( num int4, name text ); rule to insert into b instead of a: CREATE RULE redirect AS ON insert TO a DO INSTEAD insert into b values ( new.num, new.name ); ... the INSTEAD is important! BTW: is it really necessary to list all fields in the instead part? Anyone? when finished vacuuming a, do a DROP RULE redirect; of course, when you have more/other fields in your table, you need to change rule's definition. Hope this helps, Joerg +-- Science & Engineering Applications GmbH --+ | | | Joerg Hessdoerfer | | Leading SW developer Phone:+49 (0)2203-962211 | | S.E.A GmbH Fax: -962212 | | D-51147 KoelnInternet: [EMAIL PROTECTED] | |http://www.sea-gmbh.com| +---+
[SQL] sequences in functions
I am having problems referencing sequeces in a function, I think because of
the '' characters. The function I am creating is a follows: -
CREATE FUNCTION InsertClient ( varchar, varchar, varchar, varchar, varchar,
varchar ) RETURNS int4 AS '
DECLARE
id INT;
BEGIN
SELECT nextval('client_seq') INTO id;
INSERT INTO client (client_id, last_name, address1, country)
VALUES (id, $1, $2, $3);
INSERT INTO client_card (client_card_id, type, number, expiry_date,
client_id)
VALUES (nextval('client_card_seq'), $4, $5, $6, id);
RETURN id;
END;
' LANGUAGE 'plpgsql';
And the error message is
ERROR: parser: parse error at or near "client_seq"
EOF
Is this because of the ' ' ???
Also can you pass in a array or hash to the function?
Cheers
Graham
Re: [SQL] sequences in functions
Hello Graham,
Friday, August 18, 2000, 6:24:15 PM, you wrote:
GV> I am having problems referencing sequeces in a function, I think because of
GV> the '' characters. The function I am creating is a follows: -
GV> CREATE FUNCTION InsertClient ( varchar, varchar, varchar, varchar, varchar,
GV> varchar ) RETURNS int4 AS '
GV> DECLARE
GV> id INT;
GV> BEGIN
GV> SELECT nextval('client_seq') INTO id;
GV> INSERT INTO client (client_id, last_name, address1, country)
GV> VALUES (id, $1, $2, $3);
GV> INSERT INTO client_card (client_card_id, type, number, expiry_date,
GV> client_id)
GV> VALUES (nextval('client_card_seq'), $4, $5, $6, id);
GV> RETURN id;
GV> END;
GV> ' LANGUAGE 'plpgsql';
GV> And the error message is
GV> ERROR: parser: parse error at or near "client_seq"
GV> EOF
GV> Is this because of the ' ' ???
You must to use two quotes:
SELECT nextval(''client_seq'') INTO id;
--
Best regards,
Yurymailto:[EMAIL PROTECTED]
[SQL] update rule loops
Hi I'm trying to make a field in my table (datechanged) to automatically be updated with the value 'now()' when an update on the table occurs. plc=# create rule datechanged_radius AS ON update to radius do update radius set datechanged ='now()'; CREATE 22025360 1 plc=# update radius set destinationip = '212.055.059.001'; ERROR: query rewritten 10 times, may contain cycles This means that it's going in a loop, because the rule triggers itself. Is there another way to do this? Thanks, Poul L. Christiansen
Re: [SQL] Continuous inserts...
On Fri, 18 Aug 2000, Joerg Hessdoerfer wrote: > Good idea - I immediately tested it - rules rule! That seems to work perfectly, > and the client doesn't even see it happen (except for 'selects', one would > have to setup > a rule to return something meaningful then...). > > I did: > Two tables, a and b. > Normally, insert into a. > When Vacuuming starts, create rule on a to insert into b > Vacuum a > drop rule > copy records from b to a > vacuum b > > Why did you suppose three tables? Did I overlook something? I didn't try with vacuum, I just did a table lock and that seemed to still hang the inserts with two tables, so I figured maximum safety was adding the third table. If it works with two that's much cooler. Was this with real data or just a small test set?
Re: [SQL] update rule loops
On Fri, 18 Aug 2000, Poul L. Christiansen wrote: > Hi > > I'm trying to make a field in my table (datechanged) to automatically be > updated with the value 'now()' when an update on the table occurs. > > plc=# create rule datechanged_radius AS ON update to radius do update > radius set datechanged ='now()'; > CREATE 22025360 1 > plc=# update radius set destinationip = '212.055.059.001'; > ERROR: query rewritten 10 times, may contain cycles > > This means that it's going in a loop, because the rule triggers itself. > > Is there another way to do this? Two ways I can think of are either have a "view" where you do the work on the view, but the underlying table is named something else, which means you actually need to do an instead rule that does the update on that table and the setting of datechanged. (Not 100% sure of this, but should work). Second is use triggers. Write a pl/pgsql before update trigger. Assigning to NEW.datechanged should work I believe.
RE: [SQL] sequences in functions
I have noticed that you can only pass 16 parameters to a function, I was
therefore wondering how you can do atomic inserts (such as the function
below but with more params) using pl/pgsql if you can't pass complex data
types. Is this something that transactions are not used for or is it best
done as two seperate calls in my perl scripts?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf
Of Yury Don
Sent: 18 August 2000 15:07
To: [EMAIL PROTECTED]
Subject: Re: [SQL] sequences in functions
Hello Graham,
Friday, August 18, 2000, 6:24:15 PM, you wrote:
GV> I am having problems referencing sequeces in a function, I think because
of
GV> the '' characters. The function I am creating is a follows: -
GV> CREATE FUNCTION InsertClient ( varchar, varchar, varchar, varchar,
varchar,
GV> varchar ) RETURNS int4 AS '
GV> DECLARE
GV> id INT;
GV> BEGIN
GV> SELECT nextval('client_seq') INTO id;
GV> INSERT INTO client (client_id, last_name, address1, country)
GV> VALUES (id, $1, $2, $3);
GV> INSERT INTO client_card (client_card_id, type, number,
expiry_date,
GV> client_id)
GV> VALUES (nextval('client_card_seq'), $4, $5, $6, id);
GV> RETURN id;
GV> END;
GV> ' LANGUAGE 'plpgsql';
GV> And the error message is
GV> ERROR: parser: parse error at or near "client_seq"
GV> EOF
GV> Is this because of the ' ' ???
You must to use two quotes:
SELECT nextval(''client_seq'') INTO id;
--
Best regards,
Yurymailto:[EMAIL PROTECTED]
[SQL] Creating sequences
Is it possible to have a sequence (or something like it) when it increments alpha-numeric? Say the first value is set at A01 and it will increment to A02, A03 by default. Also, it would be good if it could be made the primary key. Adam Lang Systems Engineer Rutgers Casualty Insurance Company
[SQL] Tuple size limit.
Greetings pg world, I have been asked to look into making a relatively simple db app. in which the tuple size will quite probably grow to more than the 32kbytes limit offered by recompiling. I understand that the 7.1 release currently in CVS does not have this limitation. So I'd like to know if this 7.1 release is imminent; i.e. < ~2 to 3 months? I am right in thinking that one cannot do textual searches on BLOBs aren't' I? TNX 10^6 -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 - Text message enabled - http://www.mtnsms.com ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [GENERAL] Re: [SQL] variables in SQL??
You can create a running total provided that you have a unique sequentially increasing (or decreasing) ID for each row. See the following example: create table tran(id int primary key, price dec(8,2)); insert into tran values(1,5.00); insert into tran values(2,4.00); insert into tran values(3,10.00); insert into tran values(4,2.00); insert into tran values(5,7.00); select price, (select sum(price) from tran as d1 where d1.id <= d2.id) as "sum" from tran as d2; price | sum ---+--- 5.00 | 5.00 4.00 | 9.00 10.00 | 19.00 2.00 | 21.00 7.00 | 28.00 (5 rows) Francisco wrote: > > what im trying to do is have a Sum of a colum.. as it goes forwards with the > > cursor.. > > like so: > > > > Price|Sum > > 5|5 > > 4|9 > > 10|19 > > 2|21 > > 7|28 > Craig Johannsen Critical Path Consulting, Inc. 604-762-1514 http://members.home.net/cjohan/cpath
[SQL] Re: [HACKERS] [Fwd: Optimization in C]
> This solution isn't good when there are +1 tuples in the table, it's > slowly... anybody can help me ? : Someone already responded, and asked some questions about what you are really trying to do. If you didn't get the message, let us know or check the mail archives. Regards. - Thomas
[SQL] [Fwd: Optimization in C]
This solution isn't good when there are +1 tuples in the table, it's
slowly...
anybody can help me ? :
string = "SELECT service, noeud, rubrique FROM table" ;
res = PQexec( conn, string.data() ) ;
if ( (! res) || (status = PQresultStatus( res ) !=
PGRES_TUPLES_OK) )
{
cerr << _ERROR << "Problem SELECT ! " << endl ;
cerr << _ERROR << "Error : " << PQresStatus( status ) <<
endl ;
cerr << _ERROR << "Error : " << PQresultErrorMessage(
res ) << endl ;
PQclear( res ) ;
}
else
{
for (int m=0; m < PQntuples( res ); m++)
{
service = PQgetvalue( resultat1, m, 0 ) ;
noeud = PQgetvalue( resultat1, m, 1 ) ;
rubrique = PQgetvalue( resultat1, m, 2 ) ;
commande = "SELECT SUM(date) FROM table WHERE
service='" + service +
"' AND noeud='" + noeud + "' AND rubrique='"+ rubrique + "'" ;
res1 = PQexec( conn, string.data() ) ;
if ( (! res1) || (status = PQresultStatus( res1
) != PGRES_TUPLES_OK)
)
{
cerr << _ERROR << "Problem SUM ! " <<
endl ;
cerr << _ERROR << "Error : " <<
PQresStatus( status ) << endl ;
cerr << _ERROR << "Error : " <<
PQresultErrorMessage( res1 ) << endl
;
PQclear( res1 ) ;
}
else
{
cout << _TRACE << "SUM ok." << endl ;
PQclear( res1 ) ;
}
}
PQclear( res ) ;
}
Thanks. jerome.
