Re: [SQL] pl/* overhead ...
On Wed, Oct 26, 2005 at 12:58:13AM -0300, Marc G. Fournier wrote: > Does anyone know of, or have, any comparisions of the overhead going with > something like pl/perl or pl/php vs using pl/pgsql? Benchmark results will probably depend on the type of processing you're doing. I'd expect PL/pgSQL to be faster at database operations like looping through query results, and other languages to be faster at non-database operations like text munging and number crunching, depending on the particular language's strengths. [Does quick test.] Whale oil beef hooked. PL/pgSQL just outran PL/Perl when I expected the latter to win. Hang on, let me play with it until it comes back with the results I want -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] why vacuum
"Bath, David" <[EMAIL PROTECTED]> writes: > ... Note that Sybase/MS-SQL's > check constraint model asserts the constraint BEFORE the trigger, which > discourages you from attempting to check and handle meaning of data! Er, doesn't PG do it that way too? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] why vacuum
On Wednesday 26 Oct 2005 11:52 am, Bath, David wrote: > > This guy is not worth arguing with. > D'Accord! thanks all for the clarification. in case anyone is interested in the original conversation it is here: http://ebergen.net/wordpress/?p=83 -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] automatic update or insert
tobbe wrote: Hi. I have a little problem. In a system of mine i need to insert records into table [tbStat], and if the records exist i need to update them instead and increase a column [cQuantity] for every update. I.e. the first insert sets cQuantity to 1, and for every other run cQuantity is increased. Currently i have implemented this as a stored procedure in the plpgsql language. This means that in my stored procedure i first do a select to find out if the row exists or not, then i do a insert or update depending if the row existed. Unfortunately, stored procedures seems awfully slow. And i need the application to go faster. One solution could be to implement the stored procedure in my program instead. I think that this will be atleast 50% faster than my stored procedure, so that would be ok. However, this has made me thinking. Couldn't this be done directly in SQL? Brgds Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Maybe would it be better to insert always, and to use grouping and summation when using the table. That would enable you to preserve the history of events. That's how I almost always work hth P.Jacquot ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] broken join optimization? (8.0)
chester c young wrote: in php (for example) it's frequently nice to get the structure of a table without any data, ie, pull a single row with each attribute's value is null. I use the query (dual is a table of one row ala Oracle): select m.* from dual left join mytable m on( false ); Out of curiosity, why do it this way? Does "rownum" not get set if there are no rows returned? Actually, even if it doesn't why not use: SELECT * FROM mytable WHERE true=false Surely your client interface returns the types/column-names then? It should - that's a set of 0 rows. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] converting epoch to timestamp
Hi, Can anyone tell me how to convert epoch to timestamp ? ie reverse of : SELECT EXTRACT( epoch FROM now() ); +--+ |date_part | +--+ | 1130317518.61997 | +--+ (1 row) Regds mallah. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] converting epoch to timestamp
Rajesh Kumar Mallah wrote: Hi, Can anyone tell me how to convert epoch to timestamp ? ie reverse of : SELECT EXTRACT( epoch FROM now() ); I'd start with either Google or the manuals. http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html Scroll down to the section on "epoch" here and see the example. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] converting epoch to timestamp
am 26.10.2005, um 14:35:51 +0530 mailte Rajesh Kumar Mallah folgendes: > Hi, > > Can anyone tell me how to convert epoch to timestamp ? > > ie reverse of : > > SELECT EXTRACT( epoch FROM now() ); > +--+ > |date_part | > +--+ > | 1130317518.61997 | > +--+ > (1 row) SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1130317518.61997 * INTERVAL '1 second'; HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] converting epoch to timestamp
Rajesh Kumar Mallah mentioned : => Can anyone tell me how to convert epoch to timestamp ? => => ie reverse of : => => SELECT EXTRACT( epoch FROM now() ); => +--+ => |date_part | => +--+ => | 1130317518.61997 | => +--+ Here is one way (In my case I still had to add/subtract timezone diff) select '19700101'::timestamp + foo.epoch::interval from (select extract(epoch from now())||' seconds' as epoch) foo ; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pl/* overhead ...
On Wed, 26 Oct 2005, Michael Fuhr wrote:
On Wed, Oct 26, 2005 at 12:58:13AM -0300, Marc G. Fournier wrote:
Does anyone know of, or have, any comparisions of the overhead going with
something like pl/perl or pl/php vs using pl/pgsql?
Benchmark results will probably depend on the type of processing
you're doing. I'd expect PL/pgSQL to be faster at database operations
like looping through query results, and other languages to be faster
at non-database operations like text munging and number crunching,
depending on the particular language's strengths.
[Does quick test.]
Whale oil beef hooked. PL/pgSQL just outran PL/Perl when I expected
the latter to win. Hang on, let me play with it until it comes back
with the results I want
'k, let's repharase the questions :)
Overall, I'd expect pl/pgsql to have less overhead, since its "built into"
the server ... in the case of something like pl/php or pl/perl, assuming
that I don't use any external modules, is it just as 'built in', or am I
effectively calling an external interpreter each time I run that function?
For instance, if there wasn't something like to_char() (thanks for
pointing that one out), then i could write a simple pl/perl function that
'simulated it', but itself did no db queries just a simple:
RETURN sprintf("%04d", intval);
Don't know if that made much more sense ... ?
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] SETOF RECORD RETURN VALUE
Hi I am having some problem with function that returns SETOF RECORD
Here is my function:
CREATE OR REPLACE FUNCTION test_record(text)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
p_table_name ALIAS FOR $1;
temp_rec RECORD;
v_query text;
BEGIN
v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
LOOP
RETURN NEXT temp_rec;
END LOOP;
RETURN ;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
And here is how I execute the function:
select * from test_record('field_list')
I have this error:
ERROR: a column definition list is required for functions returning
"record"
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SETOF RECORD RETURN VALUE
On 10/26/05 6:34 AM, "Christian Paul B. Cosinas" <[EMAIL PROTECTED]> wrote:
> Hi I am having some problem with function that returns SETOF RECORD
>
> Here is my function:
>
> CREATE OR REPLACE FUNCTION test_record(text)
> RETURNS SETOF RECORD AS
> $BODY$
>
>
> DECLARE
> p_table_name ALIAS FOR $1;
> temp_rec RECORD;
> v_query text;
>
> BEGIN
>
> v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
> LOOP
> RETURN NEXT temp_rec;
> END LOOP;
>
> RETURN ;
>
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> And here is how I execute the function:
> select * from test_record('field_list')
>
> I have this error:
>
> ERROR: a column definition list is required for functions returning
> "record"
Since Postgres doesn't know what to expect from your function, you have to
tell it by giving the list of columns that are actually returned:
select * from test_record('field_list') as s(a,b,c,d)
where a,b,c,d are the columns in your returned set. (ie., in your example,
if p_table_name has 5 columns, you would use "as s(a,b,c,d,e)", etc.).
See here for more detail:
http://techdocs.postgresql.org/guides/SetReturningFunctions
Sean
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] RETURNS SETOF primitive returns results in parentheses
Consider this function: CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4, "varchar") RETURNS SETOF "varchar" AS $BODY$ DECLARE aRecordID ALIAS FOR $1; aSubFieldId ALIAS FOR $2; returnValue record; subFieldNumber char(3); subFieldLetter char(1); BEGIN subFieldNumber = substr(aSubFieldId, 1, 3); subFieldLetter = substr(aSubFieldId, 4); FOR returnValue IN SELECT "subfieldValue"::varchar FROM "records_sub" WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter AND "recordId" = aRecordId LOOP RETURN NEXT returnValue; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; Now, when I do this: biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); php_get_subfield_data_repeating1 -- (Anđeli) (ofsajd) (2 rows) I have return values in parentheses. However, if I create a new type: CREATE TYPE subfield_data_type AS (subfield_data varchar); And then drop the function and recreate it like this: CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4, "varchar") RETURNS SETOF subfield_data_type AS $BODY$ ... And then when I run the function, the results are ok: biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); subfield_data --- Anđeli ofsajd (2 rows) Am I doing something wrong here? Why do I need to create type with only one member of type varchar to have results without the parentheses? Mike P.S. The subFieldValue field in the records_sub table is of type varchar(4096). -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On 10/26/05 8:38 AM, "Mario Splivalo" <[EMAIL PROTECTED]> wrote: > Consider this function: > > CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4, > "varchar") > RETURNS SETOF "varchar" AS > $BODY$ > DECLARE > aRecordID ALIAS FOR $1; > aSubFieldId ALIAS FOR $2; > > returnValue record; > subFieldNumber char(3); > subFieldLetter char(1); > > BEGIN > subFieldNumber = substr(aSubFieldId, 1, 3); > subFieldLetter = substr(aSubFieldId, 4); > > FOR returnValue IN SELECT "subfieldValue"::varchar > FROM "records_sub" > WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter > AND "recordId" = aRecordId > LOOP > RETURN NEXT returnValue; > END LOOP; > > RETURN; > END > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > > Now, when I do this: > > biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); > php_get_subfield_data_repeating1 > -- > (Anđeli) > (ofsajd) > (2 rows) Does: select * from php_get_subfield_data_repeating(1,'606a') as s(a) do what you want (single column)? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 2005-10-26 at 08:54 -0400, Sean Davis wrote: > > Now, when I do this: > > > > biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); > > php_get_subfield_data_repeating1 > > -- > > (Anđeli) > > (ofsajd) > > (2 rows) > > Does: > > select * from php_get_subfield_data_repeating(1,'606a') as s(a) > > do what you want (single column)? > Nope. I still get the results in parentheses. When I change the SETOF from varchar to my defined type, your query [with as s(a)] I get normal resutls, withouth parentheses. I clearly have solved a problem, I just need to create a type containing one member only, with the type of varchar, and instead of 'RETURNS SETOF varchar' i need to do 'RETURNS SETOF my_varchar_type'. I'm just wondering is this like that 'by design', or is it a bug. I've been reproducing this on postgres versions 7.4.8, 7.4.9, 8.0.3, 8.0.4 and 8.1beta3. Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] pl/* overhead ...
PL/pgSQL is as *internal* as for example PL/Tcl. The two are actually
pretty similar and I would expect them to perform similar, if one knows
what and how he does.
PL/pgSQL is an external shared object, loaded on call of the first func
per backend. Same for PL/Tcl.
PL/pgSQL takes pg_proc.prosrc and compiles all control structures (if,
else, loop) into a form of bytecode. Query strings are left alone until
the statements are actually executed. Tcl has a similar concept of
bytecode compilation.
PL/pgSQL turns all expressions and SQL statements into prepared SPI
plans. It short-circuits simple expressions by directly calling the node
execution, so it works with PostgreSQL's native types and operators.
Here is the big difference, PL/Tcl turns all datums into their external
string representations and then does the Tcl dual-ported-object munging
and math. However, if used right it also offers prepared SPI plans.
If the implementation of functionality results in widely similar code, I
would expect PL/pgSQL and PL/Tcl to perform similar. However, doing the
prepared SPI stuff in Tcl is a bit of work. OTOH doing extensive string
processing in PL/pgSQL is a nightmare. That difference should drive the
decision which language to use when.
Jan
On 10/26/2005 5:48 AM, Marc G. Fournier wrote:
On Wed, 26 Oct 2005, Michael Fuhr wrote:
On Wed, Oct 26, 2005 at 12:58:13AM -0300, Marc G. Fournier wrote:
Does anyone know of, or have, any comparisions of the overhead going with
something like pl/perl or pl/php vs using pl/pgsql?
Benchmark results will probably depend on the type of processing
you're doing. I'd expect PL/pgSQL to be faster at database operations
like looping through query results, and other languages to be faster
at non-database operations like text munging and number crunching,
depending on the particular language's strengths.
[Does quick test.]
Whale oil beef hooked. PL/pgSQL just outran PL/Perl when I expected
the latter to win. Hang on, let me play with it until it comes back
with the results I want
'k, let's repharase the questions :)
Overall, I'd expect pl/pgsql to have less overhead, since its "built into"
the server ... in the case of something like pl/php or pl/perl, assuming
that I don't use any external modules, is it just as 'built in', or am I
effectively calling an external interpreter each time I run that function?
For instance, if there wasn't something like to_char() (thanks for
pointing that one out), then i could write a simple pl/perl function that
'simulated it', but itself did no db queries just a simple:
RETURN sprintf("%04d", intval);
Don't know if that made much more sense ... ?
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
Mario Splivalo <[EMAIL PROTECTED]> writes: > biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); > php_get_subfield_data_repeating1 > -- > (AnÄeli) > (ofsajd) > (2 rows) > I have return values in parentheses. You're getting bit by plpgsql's perhaps-excessive willingness to convert datatypes. Your returnValue variable is not a varchar, it is a record that happens to contain one varchar field. When you do "RETURN NEXT returnValue", plpgsql has to coerce that record value to varchar, and it does that by converting the record value to text ... which produces the parenthesized data format specified at http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604 regards, tom lane ---(end of broadcast)--- TIP 1: 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] why vacuum
On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: > hi, > i was in a minor flame war with a mysql guy - his major grouse was that > 'I wouldnt commit mission critical data to a database that needs to be > vacuumed once a week'. So why does pg need vacuum? The absolutely funniest thing about what this guy is saying is that he seems rather ignorant of the behaviour of innodb tables. They have another name for the vacuum command there. It's: ALTER TABLE tbl_name ENGINE=INNODB Which rebuilds the whole fraggin's table, with an exclusive lock. and guess what innodb does if you don't run this command every so often? Can you guess yet? Yep, that's right, it just keeps growing and growing and growing. Hell, innodb isn't any better than the original mvcc implementation postgresql had when vacuums were all full and took exclusive locks. But at least with PostgreSQL it was a well documented issue, and was mentioned in the administrative section of the docs, so you knew you had to do it. It's kind of tucked away in the innodb section of the mysql docs, and most mysql folks don't even know they need to do it, since they almost all use myisam table types. If someone is more worried about postgresql's non-blocking, easily scheduled vacuuming, but is using myisam tables, and a database that by default allows numeric overflows to just insert the maximum possible value, I wouldn't trust them with handling my paycheck with their fan-boy database. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 26 Oct 2005, Mario Splivalo wrote: > Consider this function: > > CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4, > "varchar") > RETURNS SETOF "varchar" AS > $BODY$ > DECLARE > aRecordID ALIAS FOR $1; > aSubFieldId ALIAS FOR $2; > > returnValue record; > subFieldNumber char(3); > subFieldLetter char(1); > > BEGIN > subFieldNumber = substr(aSubFieldId, 1, 3); > subFieldLetter = substr(aSubFieldId, 4); > > FOR returnValue IN SELECT "subfieldValue"::varchar > FROM "records_sub" > WHERE "fieldTag" = subFieldNumber AND > "subfieldTag" = subFieldLetter > AND "recordId" = aRecordId > LOOP > RETURN NEXT returnValue; I think the root cause is that you're not returning a varchar here, but instead a record containing a varchar (if I return next returnValue."subfieldValue" I don't seem to get parens). I'm not sure why it's allowing you to do so, though, it seems like that shouldn't match the return type. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] select best price
Hi folks I've got a table holding item code(cs_id), supplier a/c (co_id) , and price (cs_price). How can I select the rows containing the lowest price for each item code? I've tried various forms of min() etc and know it must be simple but I'm stumped. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Combining two SELECTs by same filters
Hi,
I've a table like:
=> SELECT dt FROM sales WHERE id = 2;
dt
2005-10-25 21:43:35.870049
2005-10-25 21:43:36.254122
2005-10-25 21:43:36.591196
2005-10-25 21:43:36.893331
2005-10-25 21:43:37.265671
2005-10-25 21:43:37.688186
2005-10-25 22:25:35.213171
2005-10-25 22:25:36.32235
(8 rows)
And I want to collect the count of sales at hour = 21 and hour = 22.
For this purpose, I'm using below SELECT query:
=> SELECT
-> (SELECT count(id) FROM sales
-> WHERE id = 2
-> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
-> (SELECT count(id) FROM sales
-> WHERE id = 2
-> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
?column? | ?column?
--+--
6 |2
(1 row)
Isn't it possible to combine these two SELECTs as one. Because one of
their filters are same: id = 2. I'm just trying to avoid making 2
scans with nearly same filters.
Regards.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] why vacuum
On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: > hi, > i was in a minor flame war with a mysql guy - his major grouse was that > 'I wouldnt commit mission critical data to a database that needs to be > vacuumed once a week'. So why does pg need vacuum? Oh man oh man. After reading the article, I realized he was saying that he wouldn't trust PostgreSQL to replace Oracle. He apparently wouldn't trust MySQL to replace oracle either. But, the next time someone says that slony is a toy add on, and MySQL has REAL replication, point them to THIS page on the same blog: http://ebergen.net/wordpress/?p=70 In short, it basically shows that MySQL replication is incredibly fragile, and not fit for production on any real system. The lack of system wide transaction support, like postgresql has, makes the problem he outlines that much worse. The hoops people will jump through to use their favorite toys... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select best price
On Wednesday 26 October 2005 4:21 pm, Gary Stainburn wrote: > Hi folks > > I've got a table holding item code(cs_id), supplier a/c (co_id) , and > price (cs_price). > > How can I select the rows containing the lowest price for each item > code? > > I've tried various forms of min() etc and know it must be simple but > I'm stumped. > > Gary I've come up with the select below. Is there a better/more efficient way of doing this? select cp.cs_id, from cons_price_details cp, (select cs_id, min(cs_price) as cs_price from cons_price_details group by cs_id ) v where cp.cs_id = v.cs_id and cp.cs_price = v.cs_price; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Combining two SELECTs by same filters
On Wed, 2005-10-26 at 10:16, Volkan YAZICI wrote:
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> --+--
> 6 |2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one. Because one of
> their filters are same: id = 2. I'm just trying to avoid making 2
> scans with nearly same filters.
Do something like this:
select count(id)
from sales
where id=2 and
dt between 'firstdatehere' and 'lastdatehere'
group by date_trunc('hour', dt);
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] why vacuum
On Wed, 2005-10-26 at 11:09, Jan Wieck wrote: > On 10/26/2005 11:19 AM, Scott Marlowe wrote: > > > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: > >> hi, > >> i was in a minor flame war with a mysql guy - his major grouse was that > >> 'I wouldnt commit mission critical data to a database that needs to be > >> vacuumed once a week'. So why does pg need vacuum? > > > > Oh man oh man. After reading the article, I realized he was saying that > > he wouldn't trust PostgreSQL to replace Oracle. He apparently wouldn't > > trust MySQL to replace oracle either. > > > > But, the next time someone says that slony is a toy add on, and MySQL > > has REAL replication, point them to THIS page on the same blog: > > > > http://ebergen.net/wordpress/?p=70 > > > > You must have missed the FAQ and other side notes about replication in > the MySQL manual. Essentially MySQL replication is nothing but a query > duplicating system, with the added sugar of taking care of now() and > some other non-deterministic things, but not all of them. > > Non-deterministic user defined procedures, functions and triggers will > simply blow MySQL's sophisticated replication apart. True, but I never expected a CTRL-C to the mysql command line to break replication. Even for MySQL's lackadaisical behaviour, that's pretty far out. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] why vacuum
On 10/26/2005 11:19 AM, Scott Marlowe wrote: On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: hi, i was in a minor flame war with a mysql guy - his major grouse was that 'I wouldnt commit mission critical data to a database that needs to be vacuumed once a week'. So why does pg need vacuum? Oh man oh man. After reading the article, I realized he was saying that he wouldn't trust PostgreSQL to replace Oracle. He apparently wouldn't trust MySQL to replace oracle either. But, the next time someone says that slony is a toy add on, and MySQL has REAL replication, point them to THIS page on the same blog: http://ebergen.net/wordpress/?p=70 You must have missed the FAQ and other side notes about replication in the MySQL manual. Essentially MySQL replication is nothing but a query duplicating system, with the added sugar of taking care of now() and some other non-deterministic things, but not all of them. Non-deterministic user defined procedures, functions and triggers will simply blow MySQL's sophisticated replication apart. Jan In short, it basically shows that MySQL replication is incredibly fragile, and not fit for production on any real system. The lack of system wide transaction support, like postgresql has, makes the problem he outlines that much worse. The hoops people will jump through to use their favorite toys... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] why vacuum
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: >> i was in a minor flame war with a mysql guy - his major grouse was that >> 'I wouldnt commit mission critical data to a database that needs to be >> vacuumed once a week'. So why does pg need vacuum? > Oh man oh man. After reading the article, I realized he was saying that > he wouldn't trust PostgreSQL to replace Oracle. Well, that's a slightly more respectable point of view, but Oracle has surely got its own set of gotchas ... doesn't it still have issues if you run a transaction that's large enough to overrun the fixed-size rollback areas (or whatever they call them)? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] broken join optimization? (8.0)
> Tom Lane <[EMAIL PROTECTED]> wrote: >> Michael Fuhr <[EMAIL PROTECTED]> writes: > > On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote: > >> in php (for example) it's frequently nice to get the structure of > >> table without any data, > > > Have you considered "SELECT * FROM mytable LIMIT 0"? > > Indeed. i think i misled: the goal is to retrieve _one_ row where the value of each attribute is null. this can be done laborously using meta data, but is done quite niftily using a left join against one row. > > I see the same behavior in the latest 8.1beta code. Maybe one of > > the developers will comment on whether optimizing that is a simple > > change, a difficult change, not worth changing because few people > > find a use for it, or a behavior that can't be changed because of > > something we're not considering. > > Not worth changing --- why should we expend cycles (even if it only > takes a few, which isn't clear to me offhand) on every join query, to > detect what's simply a brain-dead way of finding out table structure? again, the goal is a quick way to retrieve one row from a table where each attribute value is null, NOT to get the table structure. > I can't think of any realistic scenarios for a constant-false join > clause. i would like a better idea on how to retrieve one row from a table where the value of each attribute is null - i felt this a perfectly good use of sql. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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] Combining two SELECTs by same filters
On Wed, Oct 26, 2005 at 06:16:13PM +0300, Volkan YAZICI wrote:
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> --+--
> 6 |2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one.
If you can accept multiple rows instead of multiple columns then
one way would be to group by the hour:
SELECT date_trunc('hour', dt) AS hour, count(*)
FROM sales
WHERE id = 2
AND date_trunc('hour', dt) IN ('2005-10-25 21:00:00', '2005-10-25 22:00:00')
GROUP BY hour
ORDER BY hour;
hour | count
-+---
2005-10-25 21:00:00 | 6
2005-10-25 22:00:00 | 2
(2 rows)
Here's another possibility, but I find it a bit ugly:
SELECT sum(CASE date_trunc('hour', dt)
WHEN '2005-10-25 21:00:00' THEN 1
ELSE 0
END) AS count1,
sum(CASE date_trunc('hour', dt)
WHEN '2005-10-25 22:00:00' THEN 1
ELSE 0
END) AS count2
FROM sales
WHERE id = 2;
count1 | count2
+
6 | 2
(1 row)
If you're looking for the fastest method then use EXPLAIN ANALYZE
on each to see what works best on your data set.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] why vacuum
On Wed, 2005-10-26 at 11:12, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: > >> i was in a minor flame war with a mysql guy - his major grouse was that > >> 'I wouldnt commit mission critical data to a database that needs to be > >> vacuumed once a week'. So why does pg need vacuum? > > > Oh man oh man. After reading the article, I realized he was saying that > > he wouldn't trust PostgreSQL to replace Oracle. > > Well, that's a slightly more respectable point of view, but Oracle has > surely got its own set of gotchas ... doesn't it still have issues if > you run a transaction that's large enough to overrun the fixed-size > rollback areas (or whatever they call them)? Yep, and it also has the "snapshot too old" issue for long running transactions (long running meaning how many other things have happened since it started, not length of time). Imagine starting a backup in postgresql, getting an hour into it and suddenly it ends because 10,000,000 rows have been inserted while it was going. That kind of error. My reply on that forum pointed out that EVERY database has gotchas, and to pretend that your database's gotchas are ok but some other database's gotchas are unacceptable is disingenuous at best. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] broken join optimization? (8.0)
chester c young <[EMAIL PROTECTED]> writes: > i think i misled: the goal is to retrieve _one_ row where the value of > each attribute is null. Er, what for? There's no data content in that, by definition. Why not retrieve zero rows and look at the metadata anyway? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] SUM not matching up on a JOIN
The trans table contains the stub for each transaction and the trans_item table contains all the items belonging to the transaction. I need to be able to pull categorized reports for items and have all of the totals less the discounts match up with the total from the stubs for a given period. Why is my discount total different when I left join the trans table to the totals?
mg=# SELECT SUM(subtotal+tax) AS total, SUM(discount) AS discount FROM trans WHERE DATE_TRUNC('DAY',stamp)='20051010';
total | discount
+--
438.35 | 9.75
(1 row)
mg=# SELECTsum(item_price+round(item_price*item_tax,2)*qty) as total, sum(t.discount)
mg-# FROM trans_item ti
mg-# LEFT JOIN trans t
mg-# ONti.trans_id=t.id
mg-# WHERE date_trunc('day',t.stamp)='20051010';
total | discount
+--
444.10 |14.52
mg=# \d trans
Table "public.trans"
Column |Type | Modifiers
---+-+---
id| integer | not null default nextval('public.trans_id_seq'::text)
stamp | timestamp without time zone | default now()
trans_type_id | integer | not null
subtotal | numeric(6,2)| default 0.00
tax | numeric(6,2)| default 0.00
discount | numeric(6,2)| default 0.00
total_cash| numeric(6,2)| default 0.00
total_credit | numeric(6,2)| default 0.00
total_check | numeric(6,2)| default 0.00
total_gift| numeric(6,2)| default 0.00
mg=# \d trans_item
Table "public.trans_item"
Column | Type | Modifiers
+--+
id | integer | not null default nextval('public.trans_item_id_seq'::text)
trans_id | integer | not null
parent | integer |
qty| integer | not null default 1
item_sku | text | not null
item_price | numeric(5,2) |
item_tax | numeric(4,4) |
Re: [SQL] broken join optimization? (8.0)
> Tom Lane <[EMAIL PROTECTED]> wrote: > chester c young <[EMAIL PROTECTED]> writes: > > i think i misled: the goal is to retrieve _one_ row where the value > of each attribute is null. > > Er, what for? There's no data content in that, by definition. Why > not retrieve zero rows and look at the metadata anyway? > with a form that is used for CRUD, values are filled in from a record (either an object or array). when creating, you want an empty record so that form.item values are set to null. makes for much easier programming and ensures all variables are defined. retrieving the metadata and then creating the record seems like a lot of work when the whole thing can be done with one select (which would needed in any case to get the metadata). __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Combining two SELECTs by same filters
On Wed, Oct 26, 2005 at 18:16:13 +0300,
Volkan YAZICI <[EMAIL PROTECTED]> wrote:
> And I want to collect the count of sales at hour = 21 and hour = 22.
> For this purpose, I'm using below SELECT query:
>
> => SELECT
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 21:00:00'),
> -> (SELECT count(id) FROM sales
> -> WHERE id = 2
> -> AND date_trunc('hour', dt) = '2005-10-25 22:00:00');
> ?column? | ?column?
> --+--
> 6 |2
> (1 row)
>
> Isn't it possible to combine these two SELECTs as one. Because one of
> their filters are same: id = 2. I'm just trying to avoid making 2
> scans with nearly same filters.
Use an OR clause when checking the time. You will need to enclose it
in parenthesis because AND binds tighter than OR.
For consecutive hours you could use a range test. (In fact you could use
a range test even for one hour and it might be fasterdepending on your
data and what indexes you have.)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] select best price
On Wed, Oct 26, 2005 at 16:38:48 +0100, Gary Stainburn <[EMAIL PROTECTED]> wrote: > On Wednesday 26 October 2005 4:21 pm, Gary Stainburn wrote: > > Hi folks > > > > I've got a table holding item code(cs_id), supplier a/c (co_id) , and > > price (cs_price). > > > > How can I select the rows containing the lowest price for each item > > code? > > > > I've tried various forms of min() etc and know it must be simple but > > I'm stumped. > > > > Gary > > I've come up with the select below. Is there a better/more efficient > way of doing this? > > select cp.cs_id, from cons_price_details cp, > (select cs_id, min(cs_price) as cs_price > from cons_price_details > group by cs_id >) v >where cp.cs_id = v.cs_id and cp.cs_price = v.cs_price; If you only need one lowest price entry where there are ties, you could also use DISTINCT ON (a nonstandard Postgres extension) and ORDER BY that may execute faster. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 2005-10-26 at 10:40 -0400, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); > > php_get_subfield_data_repeating1 > > -- > > (Anđeli) > > (ofsajd) > > (2 rows) > > > I have return values in parentheses. > > You're getting bit by plpgsql's perhaps-excessive willingness to convert > datatypes. Your returnValue variable is not a varchar, it is a record > that happens to contain one varchar field. When you do "RETURN NEXT > returnValue", plpgsql has to coerce that record value to varchar, and > it does that by converting the record value to text ... which produces > the parenthesized data format specified at > http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604 Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as varchar, instead as of a record. Wich is what I should do in the first place. Thnx for pointing that out. :) Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
Mario Splivalo <[EMAIL PROTECTED]> writes: > Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as > varchar, instead as of a record. Wich is what I should do in the first > place. Or just return the correct field out of it. RETURN NEXT returnValue.fieldname; I think you may have to do it this way because the FOR loop wants a record variable. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 2005-10-26 at 15:45 -0400, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as > > varchar, instead as of a record. Wich is what I should do in the first > > place. > > Or just return the correct field out of it. > > RETURN NEXT returnValue.fieldname; > > I think you may have to do it this way because the FOR loop wants a > record variable. Yes, you're right. Funny implementation of the FOR loop :) Mike ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SUM not matching up on a JOIN
Tyler Kellen <[EMAIL PROTECTED]> writes: > Why is my discount total different when I > left join the trans table to the totals? Are you sure that trans_item.trans_id is unique and accounts for all the existing values of trans.id? I don't see any unique index or foreign key constraints in your \d output, so the database isn't enforcing that condition for you ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL Functions
On Oct 21, 2005, at 9:19 AM, [EMAIL PROTECTED] wrote: I have been trying to find a way to return more than one but different types of variables. How do I return more than one but mix types of variables. Any help is appriaciated. Thanks; In PostgreSQL 8.1, you'll have output parameters available. http://developer.postgresql.org/docs/postgres/plpgsql.html#PLPGSQL- OVERVIEW -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Open Source Solutions. Optimized Web Development. http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Yes, pg does triggers first before asserting check constraints! Was Re: [SQL] why vacuum
Tom,
After I wrote
> > Sybase/MS-SQL's check constraint model asserts the constraint
> > BEFORE the trigger, which discourages you from attempting to>
> > check and handle meaning of data!
you wrote (2005-10-26 17:00)
> Er, doesn't PG do it that way too?
Well, it works for me! In this case (with examples and caveats
below), postgresql (and Oracle) have got it right, which is a BIG
reason why you never get an Oracle guy to define Sybase/MS-SQL
systems - the other big reason is when you look at the sybase/mssql
nestlevel internal parameter - got the scars to prove it!
Quite frankly, if pg did not allow you to tidy things during
pre-insert and pre-update triggers, I wouldn't be so keen on it,
and stick to Oracle.
My general approach is:
1) Pre-* triggers are for tidying up the data to cover for
what is obviously a typo by user and their intent is clear
2) Post-* triggers are for propagating required data changes,
i.e. implications such as updating the "current balance"
attribute in a "customer account" record whenever the
dollar value in a transaction detail record changes.
Let me give a simple example:
1) Define attribute x as a varchar(whatever).
2) Ensure x has no leading/trailing whites
... CHECK ((NOT (X ~ '^[ \t\n\r]')) AND (NOT (X ~ '[ \t\n\r]$')))
3) During "BEFORE INSERT" and "BEFORE UPDATE" (rowlevel) triggers, include
NEW.x := btrim(NEW.x, ' \t\n\r');
4) INSERT INTO y (x, ...) VALUES ('\tblah blahdy blah\n', ...)
5) SELECT x FROM y WHERE ...
Get 'blah blahdy blah' back.
Any processing overhead is trivial compared to the time wasted by
users, by a dba when the user's complain, or undesired application
behaviour when developers make invalid assumptions about the data.
Another useful example, based on my opinion/experience that
any change of arbitrary primary keys is imnsho wrong-headed
and recoverable, I usually do the following in a pre-update
row-level trigger, especially when pk is set from a sequence:
NEW.pk := OLD.pk ;
or are least
NEW.pk := coalesce(NEW.pk, OLD.pk)
Caveat:
This approach DOES NOT WORK if we
1) define a domain (z) as a varchar(whatever),
2) put the constraint on z
3) use domain z as the datatype for x
4) attempt to change x during pre* rowlevel triggers as above
as pg barfs as soon as you assign a value to a domain that
breaks its constraint.
This caveat prompted my recent question on this list about the
possibility of a "pre-assert trigger" on a domain. (Version 9?
Pretty please with sugar on top?).
Wow! pg is even smarter than even YOU thought Tom! For this to
be the case, you guys must be excellent designers and coders,
and I tips my lid to you. Bugs are common, serendipitous useful
features are almost as rare as neonates with teeth.
--
[EMAIL PROTECTED]
Question for the day: delta(dummies_guide, executive_summary)=?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: Yes, pg does triggers first before asserting check constraints! Was Re: [SQL] why vacuum
"Bath, David" <[EMAIL PROTECTED]> writes: > you wrote (2005-10-26 17:00) >>> Sybase/MS-SQL's check constraint model asserts the constraint >>> BEFORE the trigger, which discourages you from attempting to> >>> check and handle meaning of data! >> Er, doesn't PG do it that way too? > Well, it works for me! You're right of course. I was confusing this with a related problem that people occasionally complain about, which is that datatype-related errors are thrown before the trigger can do anything about them. For instance, you can't stuff "abc" into a int4 field and hope that the trigger will have a chance to replace it with something valid. (Yes, people have asked for that :-() A less silly example is that domain constraints on a field of a domain type get checked before the trigger can run. regards, tom lane ---(end of broadcast)--- TIP 1: 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] why vacuum
On Wednesday 26 October 2005 17:44, Scott Marlowe wrote: > On Tue, 2005-10-25 at 23:45, Kenneth Gonsalves wrote: > > hi, > > i was in a minor flame war with a mysql guy - his major grouse was that > > 'I wouldnt commit mission critical data to a database that needs to be > > vacuumed once a week'. So why does pg need vacuum? > > The absolutely funniest thing about what this guy is saying is that he > seems rather ignorant of the behaviour of innodb tables. They have > another name for the vacuum command there. It's: > > ALTER TABLE tbl_name ENGINE=INNODB > > Which rebuilds the whole fraggin's table, with an exclusive lock. > > and guess what innodb does if you don't run this command every so often? > > Can you guess yet? Yep, that's right, it just keeps growing and growing > and growing. Not quite so. I'm running quite a few (>50) mysql/innodb servers with database sizes raging from 500mb to 50gb, and I never had to rebuild any innodb tables this way. InnoDB uses index-based data storage and rollback segments, which makes it harder to add bloat to their databases, as compared to PG (but autovacuum is my saviour). Innodb will actually free space when you do DELETE or TRUNCATE, but still, it's tables, indexes and tablespaces will get fragmented. This gets worse over time, but it had never been a big problem for me. My databases do 50 queries/second on average, 24/7. Note - all of this can be due to my access and data change patterns; YMMV. The "only" cleanup operation I do is CHECK/OPTIMIZE, on monthly basis; it's not much better than old PG's VACUUM, as it brings mysql/innodb's performance down by 5x-10x times; same goes for almost any long-running query. I'm moving those servers to PG, due to this (concurrency) and other reasons. My top 3 reasons are: a much better concurrency (even with bg vacuums running :-), a much better planner, and PG's rich feature set. -- Best Regards, Igor Shevchenko ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] handling money type
hello developers, i have facing one major problem handling sql money dataType in the java i have tried many permutation and combination but still i dint got correct data type to use in java to pass money data
