Thread: Postgesql lib
Hello,
I am trying to write simple PG Lib on C (on Linux), it is working, bus I
have some problem with data types, maybe someone can explain me a little
bit:
1. What is wrong with my assignments (first To = PG_GETARG_TEXT_P(0),
later VARDATA(To)? Because send mail gets something like this
"lukas@xxxxx.ltB?" when I am giving "lukas@xxxxx.lt" as a function
parameter in sql query.
2. Can anyone explain use and return values of VARDATA?
3. Any more comments on code below? it works, but data passes to sendmail
is with "garbage"..?
Datum hello( PG_FUNCTION_ARGS );
PG_FUNCTION_INFO_V1( hello );
Datum
hello( PG_FUNCTION_ARGS )
text *To, *Subject, *Body;
To = PG_GETARG_TEXT_P(0);
Subject = PG_GETARG_TEXT_P(1);
Body = PG_GETARG_TEXT_P(2);
FILE *sendmail;
sendmail = popen (SENDMAILPATH, "w");
fprintf (sendmail, "To: %s\n", VARDATA(To));
fprintf (sendmail, "Subject: %s\n", VARDATA(Subject));
fprintf (sendmail, "%s\n", VARDATA(Body));
ats = pclose (sendmail);
PG_RETURN_TEXT_P( ats );
Thank you
Lukas
UAB nSoft
+370 655 10 655
http://www.nsoft.lt
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
"Lukas" <lukas@fmf.vtu.lt> writes:
> I am trying to write simple PG Lib on C (on Linux), it is working, bus I
> have some problem with data types, maybe someone can explain me a little
> bit:
You're assuming that VARDATA() produces a null-terminated string.
It doesn't. You either need to call textout to get a null-terminated
string, or fix the fprintf calls to pay attention to the actual
string length.
> ats = pclose (sendmail);
> PG_RETURN_TEXT_P( ats );
... and this part is even less likely to work. pclose returns an
integer.
regards, tom lane
Hello,
Yes, you was right about VARDATA (working code below, maybe it will help
others), the problem was that I could not find documentation for VARDATA,
maybe you can point to it?
Also, about the
ats = pclose (sendmail);
PG_RETURN_TEXT_P( ats );
Yes, ats is integer, so what is wrong here? actually it is working
normally, or I should know something more?
Code is here:
text *To, *Subject, *Body;
To = PG_GETARG_TEXT_P(0);
char *cTo = (char *) calloc(VARSIZE(To) - VARHDRSZ, sizeof(char));
strncpy(cTo, VARDATA(To), VARSIZE(To)-VARHDRSZ);
and latter:
fprintf (sendmail, "To: %s\n", cTo);
--
Lukas
UAB nSoft
http://www.nsoft.lt
Lukas at nsoft.lt
+370 655 10 655
> "Lukas" <lukas@fmf.vtu.lt> writes:
>> I am trying to write simple PG Lib on C (on Linux), it is working, bus
>> I
>> have some problem with data types, maybe someone can explain me a little
>> bit:
>
> You're assuming that VARDATA() produces a null-terminated string.
> It doesn't. You either need to call textout to get a null-terminated
> string, or fix the fprintf calls to pay attention to the actual
> string length.
>
>> ats = pclose (sendmail);
>> PG_RETURN_TEXT_P( ats );
>
> ... and this part is even less likely to work. pclose returns an
> integer.
>
> regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Hello, I would like to ask, what is the main difference between operators IN and '='. Then I use operator IN in JOIN it gives me much worse time (in my example ~3000ms) at the same time '=' gives 30ms! But the most interesting think is that at the begging (when DB was smaller) worked at the same speed as '=', why? -- Lukas UAB nSoft http://www.nsoft.lt Lukas at nsoft.lt +370 655 10 655 Here is my SQL and Analyse results: Query with '=': SELECT mok_id, COALESCE(pard_preke, prek_pavadinimas) AS preke, mok_suma_bazine/pard_kiekis, mok_suma - COALESCE(sum(ms_suma), 0)*(case when (pard_tipas=1) then 1 when (pard_tipas=2) then -1 else 0 end) , pard_tipas, pard_spausdinta FROM b_mokejimai LEFT JOIN b_pardavimai ON (pard_id=mok_pardavimas) LEFT JOIN b_preke ON (pard_prekes_id=prek_id) LEFT JOIN b_pvm ON (pard_pvm=pvm_id) LEFT JOIN b_mokejimo_budas ON (mok_budas=mb_id) LEFT JOIN b_mokejimu_sudengimai ON (mok_id = ms_mokejimas OR mok_id = ms_padengimas) WHERE mok_cekis=122970 and pard_tipas IN (1,2) GROUP BY mok_id, pard_preke, prek_pavadinimas, pard_kiekis, mok_suma, mok_suma_bazine, pvm_kodas, prek_id, pard_id, mb_kodas, pard_tipas, pard_spausdinta ORDER BY pard_tipas, preke; Query with 'IN': SELECT mok_id, COALESCE(pard_preke, prek_pavadinimas) AS preke, mok_suma_bazine/pard_kiekis, mok_suma - COALESCE(sum(ms_suma), 0)*(case when (pard_tipas=1) then 1 when (pard_tipas=2) then -1 else 0 end) , pard_tipas, pard_spausdinta FROM b_mokejimai LEFT JOIN b_pardavimai ON (pard_id=mok_pardavimas) LEFT JOIN b_preke ON (pard_prekes_id=prek_id) LEFT JOIN b_pvm ON (pard_pvm=pvm_id) LEFT JOIN b_mokejimo_budas ON (mok_budas=mb_id) LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas, ms_padengimas)) WHERE mok_cekis=122970 and pard_tipas IN (1,2) GROUP BY mok_id, pard_preke, prek_pavadinimas, pard_kiekis, mok_suma, mok_suma_bazine, pvm_kodas, prek_id, pard_id, mb_kodas, pard_tipas, pard_spausdinta ORDER BY pard_tipas, preke; As you can undestand problem is with: LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas, ms_padengimas)) Here is explain analyse plan: QUERY PLAN Sort (cost=139348.10..139348.19 rows=34 width=99) (actual time=3708.084..3708.092 rows=17 loops=1) Sort Key: b_pardavimai.pard_tipas, (COALESCE(b_pardavimai.pard_preke, b_preke.prek_pavadinimas)) Sort Method: quicksort Memory: 20kB -> HashAggregate (cost=139346.22..139347.24 rows=34 width=99) (actual time=3707.715..3707.863 rows=17 loops=1) -> Nested Loop Left Join (cost=3246.97..139345.11 rows=34 width=99) (actual time=442.818..3707.282 rows=32 loops=1) -> Hash Left Join (cost=3246.97..139335.55 rows=34 width=68) (actual time=442.807..3706.618 rows=32 loops=1) Hash Cond: (b_pardavimai.pard_pvm = b_pvm.pvm_id) -> Hash Left Join (cost=3245.90..139334.07 rows=34 width=70) (actual time=442.782..3706.509 rows=32 loops=1) Hash Cond: (b_mokejimai.mok_budas = b_mokejimo_budas.mb_id) -> Nested Loop Left Join (cost=3244.66..139332.36 rows=34 width=72) (actual time=442.723..3706.319 rows=32 loops=1) Join Filter: (b_mokejimai.mok_id = ANY (ARRAY[b_mokejimu_sudengimai.ms_mokejimas, b_mokejimu_sudengimai.ms_padengimas])) -> Nested Loop (cost=0.00..352.65 rows=34 width=65) (actual time=0.053..0.570 rows=17 loops=1) -> Index Scan using fki_mokejimo_cekis on b_mokejimai (cost=0.00..18.49 rows=40 width=26) (actual time=0.026..0.082 rows=17 loops=1) Index Cond: (mok_cekis = 122970) -> Index Scan using pk_b_pardavimai_id on b_pardavimai (cost=0.00..8.34 rows=1 width=43) (actual time=0.018..0.022 rows=1 loops=17) Index Cond: (b_pardavimai.pard_id = b_mokejimai.mok_pardavimas) Filter: (b_pardavimai.pard_tipas = ANY ('{1,2}'::integer[])) -> Materialize (cost=3244.66..5414.53 rows=145787 width=15) (actual time=0.021..92.274 rows=141135 loops=17) -> Seq Scan on b_mokejimu_sudengimai (cost=0.00..2386.87 rows=145787 width=15) (actual time=0.009..96.607 rows=141135 loops=1) -> Hash (cost=1.11..1.11 rows=11 width=6) (actual time=0.023..0.023 rows=11 loops=1) -> Seq Scan on b_mokejimo_budas (cost=0.00..1.11 rows=11 width=6) (actual time=0.005..0.012 rows=11 loops=1) -> Hash (cost=1.03..1.03 rows=3 width=6) (actual time=0.011..0.011 rows=3 loops=1) -> Seq Scan on b_pvm (cost=0.00..1.03 rows=3 width=6) (actual time=0.004..0.006 rows=3 loops=1) -> Index Scan using b_preke_pkey on b_preke (cost=0.00..0.27 rows=1 width=35) (actual time=0.014..0.015 rows=1 loops=32) Index Cond: (b_pardavimai.pard_prekes_id = b_preke.prek_id) Total runtime: 3710.591 ms -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
"Lukas" <lukas@fmf.vtu.lt> writes:
> I would like to ask, what is the main difference between operators IN and
> '='.
> Then I use operator IN in JOIN it gives me much worse time (in my example
> ~3000ms) at the same time '=' gives 30ms!
> But the most interesting think is that at the begging (when DB was
> smaller) worked at the same speed as '=', why?
Was it also on a different PG release back then?
> LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas, ms_padengimas))
> Join Filter: (b_mokejimai.mok_id = ANY
> (ARRAY[b_mokejimu_sudengimai.ms_mokejimas,
> b_mokejimu_sudengimai.ms_padengimas]))
The latest 8.2.x and 8.3.x releases contain a patch that avoids using
this construct when there are variables on the right-hand side; I think
that's your problem.
(FWIW, most people would probably say that having to write a join like
this suggests you need to refactor your database structure...)
regards, tom lane
>> I would like to ask, what is the main difference between operators IN >> and >> '='. >> Then I use operator IN in JOIN it gives me much worse time (in my >> example >> ~3000ms) at the same time '=' gives 30ms! >> But the most interesting think is that at the begging (when DB was >> smaller) worked at the same speed as '=', why? > > Was it also on a different PG release back then? No, it was done on the same database (and same DBMS). >> LEFT JOIN b_mokejimu_sudengimai ON (mok_id IN (ms_mokejimas, >> ms_padengimas)) > >> Join Filter: (b_mokejimai.mok_id = ANY >> (ARRAY[b_mokejimu_sudengimai.ms_mokejimas, >> b_mokejimu_sudengimai.ms_padengimas])) > > The latest 8.2.x and 8.3.x releases contain a patch that avoids using > this construct when there are variables on the right-hand side; I think > that's your problem. Yes, we found the solution as shown, the question is why it is happening so? And also, what this patch does? changes the way how operator IN works? > (FWIW, most people would probably say that having to write a join like > this suggests you need to refactor your database structure...) Please comment it more - what is wrong with this join? (maybe you mean that it has to many joins in one query? Also, what do you mean by "refactor"? -- Lukas UAB nSoft http://www.nsoft.lt Lukas at nsoft.lt +370 655 10 655 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Hello,
Is here any way to get START and MAXVALUE from sequence in select query.
I mean something like this:
Select startval('MySeq'); --This can not work of course.
--
Lukas
UAB nSoft
http://www.nsoft.lt
Lukas at nsoft.lt
+370 655 10 655
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
In response to Lukas :
> Hello,
>
>
> Is here any way to get START and MAXVALUE from sequence in select query.
> I mean something like this:
> Select startval('MySeq'); --This can not work of course.
select min_value, max_value from MySeq;
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hello, yes it works - select min_value, max_value from MySeq; But what about start value? I tryed to select start_value but no luck.. -- Lukas UAB nSoft http://www.nsoft.lt Lukas at nsoft.lt +370 655 10 655 > In response to Lukas : >> Hello, >> >> >> Is here any way to get START and MAXVALUE from sequence in select >> query. >> I mean something like this: >> Select startval('MySeq'); --This can not work of course. > > select min_value, max_value from MySeq; > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
In response to Lukas : > Hello, > > yes it works - select min_value, max_value from MySeq; > But what about start value? I tryed to select start_value but no luck.. min_value = start_value. Why do need this value? And, please no top-posting: A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>> yes it works - select min_value, max_value from MySeq; >> But what about start value? I tryed to select start_value but no luck.. > > min_value = start_value. > Why do need this value? Well, start value is optional start position which allows to start sequence in the middle of its interval. We use start value, and now we need to select this value.. is it possible? -- Lukas UAB nSoft http://www.nsoft.lt Lukas at nsoft.lt +370 655 10 655 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> In response to Lukas :
>> But what about start value? I tryed to select start_value but no luck..
> min_value = start_value.
Well, that's typically but not necessarily true.
FYI, 8.4 and beyond will store the start_value separately.
regards, tom lane
>> min_value = start_value. > > Well, that's typically but not necessarily true. > > FYI, 8.4 and beyond will store the start_value separately. I am using 8.3.3 - so here is no way to get start value? -- Lukas UAB nSoft http://www.nsoft.lt Lukas at nsoft.lt +370 655 10 655 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
On 2009-01-27, Lukas <lukas@fmf.vtu.lt> wrote:
> Hello,
>
>
> Is here any way to get START and MAXVALUE from sequence in select query.
> I mean something like this:
> Select startval('MySeq'); --This can not work of course.
SELECT * FROM name_of_sequence;
On 2009-01-27, Lukas <lukas@fmf.vtu.lt> wrote: > >>> min_value = start_value. >> >> Well, that's typically but not necessarily true. >> >> FYI, 8.4 and beyond will store the start_value separately. > > I am using 8.3.3 - so here is no way to get start value? whay do you want to know?