Re: [SQL] keeping last 30 entries of a log table
> I need to write a function which inserts a log entry in a log table and > only > keeps the last 30 records. I was thinking of using a subquery similar to > the > following: > > insert into log (account_id, message) values (1, 'this is a test); > delete from log where account_id = 1 and id not in ( select id from log >where account_id = 1 order by timestamp desc limit 30); > > I'm wondering if there is a more performance oriented method of doing the > delete that I'm not thinking of. > Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like: DELETE FROM log WHERE account_id = 1 AND id < ( SELECT MIN(id) FROM log WHERE account_id = 1 ORDER BY timestamp DESC LIMIT 30); I think there will be a performance difference with your method when the number of records to be deleted is huge. -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] concurrency problem
When in this situation I:1. Wait until I have enough data to do a complete commit before even bothering to save any data to the database. I want the life of my transactions to last no more than milliseconds if possible.
2. Use a BIGSERIAL for the primary keys so the IDs are assigned automatically through triggers and sequence IDs.3. Do a "SELECT currval('my_sequence') AS seq_number;" to determine what ID was assigned so I can use it on child tables.
-Aaron BonoOn 6/16/06, sathish kumar shanmugavelu <[EMAIL PROTECTED]
> wrote:Dear group
Its my mistake that i did not reveal the whole scenario. Actually within that begin and commit, i insert in 10 tables. The above said table is the key table. I fetch the consultatioin_no and add one to it, i should know this consultation_no to save the other 10 tables. because i use this number as foreign key in other tables. Also in my program, the data for that 10 tables are collected in different java classes and save coding is also there. I initiate this save coding for all the 10 forms in the one form (some main form).
so if any error occurs i have to roll back the whole transaction. Is there any method to release the lock explicitly, where postgres store this locking information. Is both stmt.execute
("commit"); con.commit(); are both same. should i have to call con.commit() method after stmt.execute("commit") Now Iam also thinking to use sequence. but please clear the above doubts.
-- Sathish Kumar.SSpireTEKOn 6/16/06, Ash Grove <
[EMAIL PROTECTED]> wrote:
>INSERT INTO rcp_patient_visit_monitor (>entry_no, patient_id, visit_date,> is_newpatient,> visit_type, is_medical,>is_review, is_labtest, is_scan,
> is_scopy, is_xray,> weight, height)>VALUES ((SELECT> coalesce(max(entry_no)+1, 1) FROM>rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
You are only working on one table so you sholdn't haveto manage a transaction or deal with explicit locking.Just let the database handle this for you with asequence. Your concurrency issues will disappear.
1) create a sequence:create sequence entry_no_sequence2) set the new sequence's value to your table'scurrent entry_no value (n):select setval('entry_no_sequence',n)3) recreate your table so that the entry_no will get
it's value from calling nextval() on your newsequence:entry_no integer not null defaultnextval('entry_no_sequence')Thereafter, when an insert is made on your table, theenry_no field will get its value from the sequence and
the sequence will be incremented. You would then dropentro_no from your insert statement and it wouldbecome something like:INSERT INTO rcp_patient_visit_monitor (patient_id, visit_date, is_newpatient, visit_type,
is_medical, is_review,is_labtest, is_scan, is_scopy, is_xray, weight,height)VALUES (?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
[SQL] any additional date_time functions?
I am working with the date_trunc() function with great success especially in the group by clause for aggregates. However, it is limited to returning "WHOLE" time units. i.e. years, months, days, hours, minutes, seconds. Are there any functions similar to date_trunc that can return variable increments i.e.: 5, 10, or 15 minutes increments, 3, 4, 6 hour increments, 1, 2 weekly increments, I imagine that the returned values would have to either be the "floor" or "ceiling" of the actual time stamps. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] concurrency problem
Locks are released when the containing transaction
commits. There is no explicit "release."
Instead of calling "begin" and "commit" as statements,
I do something more like below. As Aaron mentioned,
this is JDBC, not SQL. Sorry people.
try {
...
conn.setAutoCommit(false);
//do the insert on the table that generates the
primary key via a sequence
PreparedStatement pstmt =
conn.prepareStatement("my prepared statement");
pstmt.executeUpdate();
//your prepared statement above should do an
//insert on a table that calls nextval().
//Calling currval() below will guarantee that
you'll get
//the value created by the insert statement
//Check out the documentation on sequence
functions
//get the new primary key
String get_pkey = "{ ? = call currval('my_seq')
}";
CallableStatement = conn.prepareCall(get_pkey);
cstmt.registerOutParameter(1, Types.BIGINT);
cstmt.execute();
long new_pkey = cstmt.getLong(1);
//do all of your updates/inserts on tables using
new_pkey as a foreign key
//I like to do this in batches
Statement stmt = conn.createStatement();
stmt.addBatch("insert into... )
stmt.addBatch("update whatever set... )
stmt.executeBatch();
conn.commit();
stmt.close();
conn.close();
} catch(SQLException e1) {
//do something with error 1
if (conn != null) {
try {
conn.rollback();
} catch(SQLException e2) {
//do something with error 2
}
}
}
--- Aaron Bono <[EMAIL PROTECTED]> wrote:
> When in this situation I:
>
> 1. Wait until I have enough data to do a complete
> commit before even
> bothering to save any data to the database. I want
> the life of my
> transactions to last no more than milliseconds if
> possible.
> 2. Use a BIGSERIAL for the primary keys so the IDs
> are assigned
> automatically through triggers and sequence IDs.
> 3. Do a "SELECT currval('my_sequence') AS
> seq_number;" to determine what ID
> was assigned so I can use it on child tables.
>
> -Aaron Bono
>
> On 6/16/06, sathish kumar shanmugavelu
> <[EMAIL PROTECTED]>
> wrote:
> >
> > Dear group
> >Its my mistake that i did not reveal the whole
> scenario.
> >Actually within that begin and commit, i
> insert in 10 tables. The
> > above said table is the key table.
> >I fetch the consultatioin_no and add one to it,
> i should know this
> > consultation_no to save the other 10 tables.
> because i use this number as
> > foreign key in other tables. Also in my program,
> the data for that 10 tables
> > are collected in different java classes and save
> coding is also there. I
> > initiate this save coding for all the 10 forms in
> the one form (some main
> > form).
> > so if any error occurs i have to roll back the
> whole transaction.
> >
> > Is there any method to release the lock
> explicitly, where postgres
> > store this locking information.
> > Is both
> > stmt.execute ("commit");
> > con.commit();
> > are both same. should i have to call
> con.commit() method after
> > stmt.execute("commit")
> >
> > Now Iam also thinking to use sequence. but
> please clear the above
> > doubts.
> >
> > --
> > Sathish Kumar.S
> > SpireTEK
> >
> >
> > On 6/16/06, Ash Grove <[EMAIL PROTECTED]> wrote:
> > >
> > >
> > >
> > > >INSERT INTO rcp_patient_visit_monitor (
> > > >entry_no, patient_id,
> visit_date,
> > > > is_newpatient,
> > > > visit_type, is_medical,
> > > >is_review, is_labtest,
> is_scan,
> > > > is_scopy, is_xray,
> > > > weight, height)
> > > >VALUES ((SELECT
> > > > coalesce(max(entry_no)+1, 1) FROM
> > > >
> > >
>
rcp_patient_visit_monitor),?,current_timestamp,?,?,?,?,?,?,?,?,?,?)
> > >
> > > You are only working on one table so you
> sholdn't have
> > > to manage a transaction or deal with explicit
> locking.
> > > Just let the database handle this for you with a
> > > sequence. Your concurrency issues will
> disappear.
> > >
> > > 1) create a sequence:
> > >
> > > create sequence entry_no_sequence
> > >
> > >
> > > 2) set the new sequence's value to your table's
> > > current entry_no value (n):
> > >
> > > select setval('entry_no_sequence',n)
> > >
> > >
> > > 3) recreate your table so that the entry_no will
> get
> > > it's value from calling nextval() on your new
> > > sequence:
> > >
> > > entry_no integer not null default
> > > nextval('entry_no_sequence')
> > >
> > >
> > > Thereafter, when an insert is made on your
> table, the
> > > enry_no field will get its value from the
> sequence and
> > > the sequence will be incremented. You would then
> drop
> > > entro_no from your insert statement and it would
> > > become something like:
> > >
> > > INSERT INTO rcp_patient_visit_monitor (
> > > patient_id, visit_date, is_newpatient,
> visit_typ
