[SQL] Turn off flushing after each write
How can I control that? Where is the setting I can tweak? I checked the doc at http://www.archonet.com/pgdocs/tweak-perf.html. Couldn't find any reference to it. Thanks! -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] shared memory size
Will increasing kernel shared memory size (in linux by doing "echo 134217728 >/proc/sys/kernel/shmall; echo 134217728 >/proc/sys/kernel/shmmax) help with the speed of a complicated query with a large return set? (average 2 or more entries in return) Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(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
[SQL] C Functions
#include
#include
char *fernando(char *texto)
{
char *resultp = palloc(strlen(texto)+5);
*resultp = *texto;
strcat(resultp," mais");
return resultp;
}
gcc -shared fernando.c -o fernando.so
CREATE FUNCTION fernando (bpchar) RETURNS bpchar
AS '/u/src/tef/fernando.so' LANGUAGE 'c';
CREATE
SELECT fernando ('Teste');
ERROR: Memory exhausted in AllocSetAlloc(287341377)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] C Functions
I've never used functions in postgres, but the line *resultp = *testo; looks wrong to me. Shouldn't it be strcpy(resultp, testo); ? Regards, Patrik Kudo ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Känns det oklart? Fråga på! On Thu, 12 Jul 2001, Fernando Eduardo B. L. e Carvalho wrote: > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Functions performed on intervals
I hope this is not the wrong list for this type of question... I'm about to start development on a small app to track employee leave and vacation time. Based on a simple formula, each employee gets x number of days at the end of each month. x is a function of time-in-service and employee type: part-time, full-time, and salary. I could just write a view to display total time accumulated from their start date to current date (minus leave taken), but what happens when the employee moves from part-time to full-time? Their entire leave is recalculated with the new formula and the employee gets some extra time off. The solution I've thought of is to call a function once a month with cron to update each employee's leave balance. Is this the proper way to accomplish this task or are there better methods or approaches to getting the desired effect? Thanks for any advice you can give, Jimmie Fulton Systems Administrator Emory University School Of Medicine ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Functions performed on intervals
Jimmie, > I hope this is not the wrong list for this type of question... Nope. You're come to *exactly* the right list. > > I'm about to start development on a small app to track employee leave > and > vacation time. Based on a simple formula, each employee gets x > number of > days at the end of each month. x is a function of time-in-service > and > employee type: part-time, full-time, and salary. I could just write > a view > to display total time accumulated from their start date to current > date > (minus leave taken), but what happens when the employee moves from > part-time > to full-time? Their entire leave is recalculated with the new > formula and > the employee gets some extra time off. The solution I've thought of > is to > call a function once a month with cron to update each employee's > leave > balance. Is this the proper way to accomplish this task or are there > better > methods or approaches to getting the desired effect? Actually, I can think of at least 3 different approaches. What's "best" depends on: 1) your control over the data structure (e.g. can you add an "employee_history" table?) 2) What changes to leave time calcualtions do you want to be time-bound, and what do you want to be retroactively re-calculated for all active employees? 3) What other factors are likely to change over time. That being said, any solution you come up with will involve *some* kind of history table/fields being added to the application. It's a question of *what* kind: 1) You can add a "leave time history" that journals leave time calculations on a daily, monthly, or weekly basis; 2) You can add an "employee history" table that journals an employees status on a periodic basis; 3) You can add/extend the relational sub-tables governing the characterisitcs that are peculiar to the different types of employees (full-time, part-time, contract) (there's a good example of this in Practical Issues in Database Design by F. Pascal) to include date ranges; 4) You can even add a "leave time rule history" table to keep track of how leave time is calculated over the history of the company (e.g. what if leave time was 14 days per year through 1999, but decreased to 10 days per year in 2000?) 5) Any/all of the above. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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] Functions performed on intervals
Thanks for your response, > Actually, I can think of at least 3 different approaches. > What's "best" > depends on: > > 1) your control over the data structure (e.g. can you add an > "employee_history" table?) > 2) What changes to leave time calcualtions do you want to be > time-bound, > That being said, any solution you come up with will involve > *some* kind > of history table/fields being added to the application. I have full control over the project so it is not an issue to add fields/tables. I already know that a history will be needed, but I haven't decided how I want to implement it, yet. I've thought of several ways: 1) Having a total_leave field for each user, and having a vacation table which keeps a history of days taken for each vacation. Then subtract totals of vacation from the total_leave field in reports. 2) Having a table which includes history items of each time leave is added, and once again, a table for vacations they have taken. Subtract sums from later to former. 3) As you said, several ways... > 1) You can add a "leave time history" that journals leave time > calculations on a daily, monthly, or weekly basis; What you are saying here is that I will indeed need an external timed event (cron) to update a field/history table of some fashion, correct? This is my main question. > 3) You can add/extend the relational sub-tables governing the > characterisitcs that are peculiar to the different types of employees > (full-time, part-time, contract) (there's a good example of this in > Practical Issues in Database Design by F. Pascal) to include date > ranges; The use of subtypes does not appeal to me in this particular instance. I do now own Pascal's book though, thanks to you in a previous posting. :) > 4) You can even add a "leave time rule history" table to keep track of > how leave time is calculated over the history of the company > (e.g. what > if leave time was 14 days per year through 1999, but decreased to 10 > days per year in 2000?) > 5) Any/all of the above. > > -Josh Berkus > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(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 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Functions performed on intervals
Jimmie, > Thanks for your response, You're welcome. > What you are saying here is that I will indeed need an external timed > event > (cron) to update a field/history table of some fashion, correct? > This is my > main question. Yes. Absolutely you will. Unless, of course, your users are already forced to go though a manual month-end procedure, in which case it could be tied to that. This is actually really easy to set up: 1. Create a shell script that calls psql and a SQL script in 2; 2. Create a SQL script that calls all of the functions that you want to run on a (daily/weekly/monthly) basis. 3. Create a cron item that calls the shell script (1.). Handling errors etc. obviously adds a layer of complexity to the scripting. I also have the functions write to a schedule_log table showing what they've done. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Referencing a view?
Hi,
Is there anyway that you can reference a column in
a view for referential integrity? The problem is with the unique thing,
obviously I can't create a unique index on a view. Here is what I
have:
CREATE SEQUENCE
"addresses_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1 ;
CREATE TABLE "org_addresses" ( "id"
integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL, "orgid"
integer references orgs on delete cascade, "name" character
varying(255), "street1" character varying(255), "street2"
character varying(100), "city" character varying(100), "state"
character(2), "zip" character(10), Constraint
"org_addresses_pkey" Primary Key ("id"));
CREATE TABLE "user_addresses" ( "id"
integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL, "userid"
integer references users on delete cascade, "name" character
varying(255), "street1" character varying(255), "street2"
character varying(100), "city" character varying(100), "state"
character(2), "zip" character(10), Constraint
"user_addresses_pkey" Primary Key ("id"));
CREATE VIEW "addresses" as SELECT
user_addresses.id, user_addresses.userid, user_addresses.name,
user_addresses.street1, user_addresses.street2, user_addresses.city,
user_addresses.state, user_addresses.zip FROM user_addresses UNION SELECT
org_addresses.id, NULL::unknown, org_addresses.name, org_addresses.street1,
org_addresses.street2, org_addresses.city, org_addresses.state,
org_addresses.zip FROM org_addresses;
So this gives me a view with every address, each
with a unique id as I used the same sequence in both tables. Now what I
want to do is something like this :
CREATE TABLE orders (
id serial primary
key,
shipping_address int references
addresses(id),
.
.
);
Which of course doesn't work because
addresses as a view can't have a unique index. Any way around
this?
- James
Re: [SQL] Referencing a view?
On Thu, 12 Jul 2001, James Orr wrote: > Hi, > > Is there anyway that you can reference a column in a view for > referential integrity? The problem is with the unique thing, > obviously I can't create a unique index on a view. Here is what I > have: Not right now, and actually you still wouldn't get something that would work even with a unique index. We'd have to be able to push the constraint conditions down into the two tables that you're unioning to make it work correctly (think about the triggers on the referenced table for update and delete - which in your case with a unique index would be safe to put the triggers on all the time in both, but some view conditions would be such that that wouldn't be sufficient). You *might* be able to add the triggers manually and have it work. I haven't tried, and wouldn't guarantee it at all... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How can we match a condition among 2 diff. tables?
Hi, Consider the below... table 'employee' with unique 'emp_id', table 'salesorder' with 'emp_id' and unique 'sales_id' Remember, both tables include some more different fields. Now, i need to get the details of all employees who did receive NONE of the salesorders. ie.. i wish to select the records of table 'employee' whose 'emp_id' are not there in table 'salesorder'. I need to accompolish in a single query! Thankx in advance. Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
