[GENERAL] Check for an empty result
Hi Ho! Is there a way to check whether or not a subquery returns an empty result set? Googling with the following keywords does not help: postgre check empty result set sql check empty result Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Check for an empty result
Eus wrote: Hi Ho! Is there a way to check whether or not a subquery returns an empty result set? EXISTS SELECT blah FROM blah WHERE EXISTS (SELECT 1 FROM tablename WHERE ...); postgre check empty result set It's not postgre. It's PostgreSQL, or postgres. This matters when you're searching. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Check for an empty result
In response to Eus : Hi Ho! Is there a way to check whether or not a subquery returns an empty result set? You can use EXISTS for that: -- empty result test=*# select * from (select 1 where 1=2) foo; ?column? -- (0 rows) -- check if a result exists test=*# select exists(select * from (select 1 where 1=2) foo); ?column? -- f (1 row) test=*# select exists(select * from (select 1 where 1=1) foo); ?column? -- t (1 row) Regards, 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-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] TSearch queries with multiple languages
On Fri, 13 Feb 2009 09:55:04 +0300 (MSK) Oleg Bartunov o...@sai.msu.su wrote: contrib/btree_gin, which is under review for 8.4, will allow to create composite index like (ts_config, tsvector), so queries which specified ts_config (language) will uses this index. Grass Root protectionism of good programmers ;) Not only Oleg write very valuable code, but he really cares about his users base. I'm still sorry I haven't been able to track down the origin of a very slow gin index creation I reported months ago. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] password for postgres
i don't know y am i getting this problem when i try to start off postgres it asks me for password: i did not set any password as such except that when the first day i used template, i ahve used the below two statements ALTER USER postgres with encrypted password 'your_password'; ALTER USER postgres with encrypted password 'welcome'; but it is not accepting both the passwords i am getting incorrect password after three trials it is returning back to command prompt when i have used select * from pg_shadow; then i got md5d31faa0b92fad4e2d8e4af34a30f890b though i use this i am not able to acess i don't know what to do with this issue can any one shed light on me by explaining me what was the mistake i did or which password to use thanks for any help Regards kusuma.p -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] password for postgres
Kindly let me know two things to answer you. First, Are you trying to start postgres service or psql prompt? second, what OS is in your use.
[GENERAL] Load Testing
Hi all, Can any body tell me about tool for PostgreSQL load testing preferably freeware. Regards, Abdul Rehman.
Re: [GENERAL] Load Testing
Ashish Karalkar wrote: Abdul Rahman wrote: Hi all, Can any body tell me about tool for PostgreSQL load testing preferably freeware. Regards, Abdul Rehman. I am not sure its a freeware or not but looks promising http://bristlecone.continuent.org/HomePage --Ashish And ofcourse the PGbench which is freeware: http://www.postgresql.org/docs/current/static/pgbench.html --Ashish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Load Testing
Abdul Rahman wrote: Hi all, Can any body tell me about tool for PostgreSQL load testing preferably freeware. Regards, Abdul Rehman. I am not sure its a freeware or not but looks promising http://bristlecone.continuent.org/HomePage --Ashish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] temporary table problem
Hi,
I am using EnterpriseDB(8.1) here is my individual procedure code written as
like:
Create or replace Procedure sp_leaveSummary(
op_viewSummary OUT sys_refcursor,
op_errormessage OUT varchar
)
is
tCasual bigint:=0;
tSick bigint:=0;
tEarned bigint:=0;
uCasual bigint:=0;
uSickbigint:=0;
uEarned bigint:=0;
aCasual bigint:=6;
aSickbigint:=8;
aEarned bigint:=15;
lCasual bigint:=0;
lSickbigint:=0;
lEarned bigint:=0;
Begin
Execute immediate 'Create temporary table viewsummary(legend varchar,casual
bigint,sick bigint,earned bigint)';
for i in 1..4 loop
if i=1
then
insert into viewsummary(legend,casual,sick,earned) values
('Total',tCasual,tSick,tEarned);
elsif i=2
then
insert into viewsummary(legend,casual,sick,earned) values
('Used',uCasual,uSick,uEarned);
elsif i=3
then
insert into viewsummary(legend,casual,sick,earned) values
('Available',tCasual-uCasual,tSick-uSick,tEarned-uEarned);
elsif i=4
then
insert into viewsummary(legend,casual,sick,earned) values ('Loss Of
Pay',lCasual,lSick,lEarned);
end if;
end loop;
Open op_viewSummary for select legend,casual,sick,earned from viewsummary;
Exception
WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE('Error Message : '||SQLERRM||'Error Code :
'||SQLCODE);
op_errormessage:=('Error Message :'||Sqlerrm);
dbms_output.put_line(op_errormessage);
End;
--
Here I am executing the procedure as follows:
---
declare
opr sys_refcursor;
legend varchar;
casual bigint;
sick bigint;
earned bigint;
opm varchar;
begin
sp_leaveSummary(41,opr,opm);
loop
fetch opr into legend,casual,sick,earned;
exit when opr%notfound;
dbms_output.put_line(legend||' '||casual||' '||sick||' '||earned);
dbms_output.put_line(opm);
end loop;
end;
---
Out put is :
---
INFO: Total 6 8 15
INFO:
INFO: Used 38 12 58
INFO:
INFO: Available -32 -4 -43
INFO:
INFO: Loss Of Pay 0 0 0
INFO:
EDB-SPL Procedure successfully complete
---
Now my question is from DB side there's no error, But from UI (java) side
while calling the procedure
they are getting the null refcursor and as well as op_errormessage out
parameter getting the error message
like viewsummary table is already exists.
1) How to destroy the temporary table.
2) How to return the values to the refcursor with out any errors.
Thanks Regards,
-Sanjeev (MIT)
Re: [GENERAL] password for postgres
On Friday 13 February 2009 2:18:32 am Kusuma Pabba wrote: i don't know y am i getting this problem when i try to start off postgres it asks me for password: Are trying to start the Postgres program or are you trying to connect to an already running server? i did not set any password as such except that when the first day i used template, i ahve used the below two statements ALTER USER postgres with encrypted password 'your_password'; ALTER USER postgres with encrypted password 'welcome'; If you did it that order then your password for connecting should be 'welcome'. User/role information is cluster wide. If you entered the above to access the template then it is in effect for all databases in the cluster. Are you connecting as the user postgres or another user? but it is not accepting both the passwords i am getting incorrect password after three trials it is returning back to command prompt What is the error message that you are getting? Have you set up the pg_hba.conf file correctly? See http://www.postgresql.org/docs/8.3/interactive/client-authentication.html for more information. when i have used select * from pg_shadow; then i got md5d31faa0b92fad4e2d8e4af34a30f890b I am assuming this is for the user postgres. though i use this i am not able to acess i don't know what to do with this issue can any one shed light on me by explaining me what was the mistake i did or which password to use thanks for any help Regards kusuma.p -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [findings] minimal open source e-commerce software for pg
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 After googling and downloading some of the most popular open source ecommerce software I didn't find any that works out of the box with PostgreSQL. Have you looked at Interchange? http://en.wikipedia.org/wiki/Interchange_(software) http://www.icdevgroup.org/ - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200902131158 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkmVps8ACgkQvJuQZxSWSsguGACgvcDGsH5u0rbwQLMpTTFFQ4gP pEgAniVozsijGXjaiky/L5H5GUvPOMnl =2kf3 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] clearing the buffer cache
Hi, I was doing some performance checks and wondered what the best way to clear out the shared buffers is? With the recent improvements in buffer management it appears a simple SELECT * FROM large_table; doesn't help here! I was hoping for a function I could call, or maybe some variable I write to, that would cause the contents to be invalidated. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [findings] minimal open source e-commerce software for pg
On Fri, 13 Feb 2009 16:59:52 - Greg Sabino Mullane g...@turnstep.com wrote: After googling and downloading some of the most popular open source ecommerce software I didn't find any that works out of the box with PostgreSQL. Have you looked at Interchange? http://en.wikipedia.org/wiki/Interchange_(software) http://www.icdevgroup.org/ Some more stuff to learn from... Anyway I decided I really liked my larger e-commerce project and adapted it so that it could fit even for b2c, smaller sites. That stuff is GPL I just don't dare to publish it till it will be clean enough I won't see a too fast improvement I can't handle on the project that originated all this. So, sooner or later there will be one more Free e-commerce project based on postgresql around. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] clearing the buffer cache
On Fri, 2009-02-13 at 17:49 +, Sam Mason wrote: Hi, I was doing some performance checks and wondered what the best way to clear out the shared buffers is? With the recent improvements in buffer management it appears a simple SELECT * FROM large_table; doesn't help here! I was hoping for a function I could call, or maybe some variable I write to, that would cause the contents to be invalidated. Restart the database. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] clearing the buffer cache
On Fri, Feb 13, 2009 at 12:52:21PM -0500, Brad Nicholson wrote: On Fri, 2009-02-13 at 17:49 +, Sam Mason wrote: I was doing some performance checks and wondered what the best way to clear out the shared buffers is? With the recent improvements in buffer management it appears a simple SELECT * FROM large_table; doesn't help here! I was hoping for a function I could call, or maybe some variable I write to, that would cause the contents to be invalidated. Isn't there anything faster than this? I was hoping to run many thousands of small (i.e. 100ms or less) queries and if it takes a couple of seconds to restart the database I'm not going to be able to do this. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] clearing the buffer cache
[ Sorry Brad, I didn't mean to delete your response! lets try again. ] On Fri, Feb 13, 2009 at 12:52:21PM -0500, Brad Nicholson wrote: On Fri, 2009-02-13 at 17:49 +, Sam Mason wrote: I was doing some performance checks and wondered what the best way to clear out the shared buffers is? With the recent improvements in buffer management it appears a simple SELECT * FROM large_table; doesn't help here! I was hoping for a function I could call, or maybe some variable I write to, that would cause the contents to be invalidated. Restart the database. Isn't there anything faster than this? I was hoping to run many thousands of small (i.e. 100ms or less) queries and if it takes a couple of seconds to restart the database I'm not going to be able to do this. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] clearing the buffer cache
Isn't there anything faster than this? I was hoping to run many thousands of small (i.e. 100ms or less) queries and if it takes a couple of seconds to restart the database I'm not going to be able to do this. Are you forgetting the OS's file system cache? That will also have a huge effect on performance, and so you'll need to clear it as well. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] clearing the buffer cache
On Fri, Feb 13, 2009 at 11:13:36AM -0700, Scott Ribe wrote: I was hoping to run many thousands of small (i.e. 100ms or less) queries and if it takes a couple of seconds to restart the database I'm not going to be able to do this. Are you forgetting the OS's file system cache? That will also have a huge effect on performance, and so you'll need to clear it as well. With recent versions of Linux you can flush the system's buffer cache by doing: # echo 3 /proc/sys/vm/drop_caches This is pretty quick; I do this, wait a small amount of time just in case and then start a new psql session. I'm currently having to put a restart of postgres before this flush as well which is slowing things down a lot. I'm not showing any statistically significant difference between the first run and subsequent runs which would suggest that I'm either all OK, or all wrong! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remote Connection
Hi John You are right about the router. I now have PG Admin displaying the server. However it still won't connect even with the firewall off. Actually, the message says the the connection was 'actively' rejected. I am not sure what you mean by this IP format 192.168.1.0/24. I notice that the pg_hba file also includes an adress in similar format - 127.0.0.1/32. How does this compare to my address 192.168.1.100? Thanks in advance. Bob - Original Message - From: John R Pierce pie...@hogranch.com To: PostgreSQL pgsql-general@postgresql.org Sent: Thursday, February 12, 2009 3:26 PM Subject: Re: [GENERAL] Remote Connection Bob Pawley wrote: Hi I've been operating on localhost successfully for some time. I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router. Is this possible? My first attempts have been with Postgresql Admin. I changed host to the IP address of the computer and input the other info. I also ran - listen virtual; on the receiving server. It hasn't connected successfully. I get the message 'Server not listening' presumably, when you say 'through a router', you mean, both client and server systems are on the LAN side of the router, so its just a local area network? A) make sure LISTEN_ADDRESS is * and not localhost in postgresql.conf B) if the server has a firewall make sure you allow inbound port 5432/tcp C) make sure pg_hba.conf on the postgres server has a line similar to... host all all 192.168.1.0/24 md5 where 192.168.1.* is the IP address of your local network. md5 says to use password authentication. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remote Connection
On 13/02/2009 18:24, Bob Pawley wrote: I am not sure what you mean by this IP format 192.168.1.0/24. Hi Bob, That's what's called a CIDR address - have a look at http://en.wikipedia.org/wiki/CIDR - and in this instance it means that any host in the address range 192.168.1.1 - 192.168.1.254 with a network mask of 255.255.255.0 should be able to connect. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Load Testing
On Fri, Feb 13, 2009 at 4:48 AM, Ashish Karalkar ashis...@synechron.com wrote: Ashish Karalkar wrote: Abdul Rahman wrote: Hi all, Can any body tell me about tool for PostgreSQL load testing preferably freeware. Regards, Abdul Rehman. I am not sure its a freeware or not but looks promising http://bristlecone.continuent.org/HomePage --Ashish And ofcourse the PGbench which is freeware: http://www.postgresql.org/docs/current/static/pgbench.html Yep. pgbench is kind of my basic acceptance testing benchmark. If you've got a 16 core 128G ram machine hitched onto a 100+15k5 SAS disk san array and you're getting 20 tps on pgbench there's not much use in running other benchmarks until you figure out what's so wrong. It's also good for applying burn in loads over long periods. Nothing like a week of running pgbench to find problems with RAID controllers, drives, memory, cpus, cooling, power supplies or kernels. I had a kernel bug on a server last year that took about 12 hours of heavy pgbench to show up. Had a bad RAID controller that took 24 to 36 hours of pgbench to hang. Plus, pgbench has the ability to run custom SQL for benchmarking, so it's an easy way to build a custom test. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3 doc issue
This is probably the wrong place to mention this, but idunno: I did a search in the docs of pg_standby and was presented this link: Based on your search term, we recommend the following links: * http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_standby/ but that link gives a 404: Not Found The requested URL /cvsweb.cgi/pgsql/contrib/pg_standby/ was not found on this server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] can't figure string compare result (using also custom C function)
I have this query: (1 result) SELECT idSpecie,nome FROM specienomi WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+- 37026 | X Agropogon littoralis (Sm.) C.E. Hubb. The same query but without one condition: no results. SELECT idSpecie,nome FROM specienomi WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+-- (0 rows) I can't figure why, can someone tell me how investigate? specienomi is a view idSpecie is a numeric field (the key of another table) nome is a text field generated by a custom C function (using 18 fields (1 enumerate type, 1 boolean, 16 text). The problem arises only with particular records, when the first character of the string is generated by my function [1]. When the first character is copied from postgres parameter [2] all works fine. [1] buffer[0]='X'; buffer[1]=' '; [2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ) Thank you Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't figure string compare result (using also custom C function)
On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote: The problem arises only with particular records, when the first character of the string is generated by my function [1]. When the first character is copied from postgres parameter [2] all works fine. [1] buffer[0]='X'; buffer[1]=' '; [2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ) I'm not much of an expert with extending PG in C; but my first suggestion would be are you null terminating the string? If you are, could you include a (cut down) portion of the code that demonstrates the problem? -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3 doc issue
On Feb 13, 2009, at 11:59 AM, Ray Stell wrote: This is probably the wrong place to mention this, but idunno: I did a search in the docs of pg_standby and was presented this link: Based on your search term, we recommend the following links: * http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_standby/ but that link gives a 404: Not Found The requested URL /cvsweb.cgi/pgsql/contrib/pg_standby/ was not found on this server. Wtih the 8.3 release docs for the contrib packages are in the Additional Supplied Modules section of the manual's appendix: http://www.postgresql.org/docs/8.3/interactive/pgstandby.html Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remote Connection
In pg_hba there is a reference to reloading postmaster using pg_ctl. Does postmaster reload when the server is restarted?? Bob - Original Message - From: Raymond O'Donnell r...@iol.ie To: Bob Pawley rjpaw...@shaw.ca Cc: John R Pierce pie...@hogranch.com; PostgreSQL pgsql-general@postgresql.org Sent: Friday, February 13, 2009 10:31 AM Subject: Re: [GENERAL] Remote Connection On 13/02/2009 18:24, Bob Pawley wrote: I am not sure what you mean by this IP format 192.168.1.0/24. Hi Bob, That's what's called a CIDR address - have a look at http://en.wikipedia.org/wiki/CIDR - and in this instance it means that any host in the address range 192.168.1.1 - 192.168.1.254 with a network mask of 255.255.255.0 should be able to connect. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't figure string compare result (using also custom C function)
Sam Mason ha scritto:
On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote:
The problem arises only with particular records, when the first
character of the string is generated by my function [1].
When the first character is copied from postgres parameter [2] all works
fine.
[1] buffer[0]='X'; buffer[1]=' ';
[2] memcpy(buffer,VARDATA(part),VARSIZE(part)-VARHDRSZ)
I'm not much of an expert with extending PG in C; but my first
suggestion would be are you null terminating the string?
the code above is only a fragment, with a 0 at the end of the buffer
there are a lot more problems, I use SET_VARSIZE().
The problem does not affect all my records, only 6/25480.
If you are, could you include a (cut down) portion of the code that
demonstrates the problem?
the problem seems to be at the start of the string:
guidebook= SELECT idSpecie,nome FROM specienomi
WHERE nome like 'X Agropogon littoralis (Sm.) C.E. Hubb.';
idspecie | nome
--+--
(0 rows)
SELECT idSpecie,nome FROM specienomi
WHERE nome like '%X Agropogon littoralis (Sm.) C.E. Hubb.';
idspecie | nome
--+-
37026 | X Agropogon littoralis (Sm.) C.E. Hubb.
(1 row)
guidebook= SELECT idSpecie,nome FROM specienomi
WHERE nome like 'X Agropogon littoralis (Sm.) C.E. Hubb.%';
idspecie | nome
--+--
(0 rows)
The function is very ripetitive (and whith italian names for variables).
I did a try with a shorter one but can't obtain the same bug (sorry).
there is a HERE near the lines that seems to be problematic.
Edoardo
- function --
char *prefissoSottospecie=subsp. ;
#define LUNGHEZZA_PREF_SS 7
char *prefissoVarieta=var. ;
#define LUNGHEZZA_PREF_VAR 5
char *prefissoSottoVarieta=subvar. ;
#define LUNGHEZZA_PREF_SVAR 8
char *prefissoForma=f. ;
#define LUNGHEZZA_PREF_FO 3
char *prefissoRace=race ;
#define LUNGHEZZA_PREF_RACE 5
char *prefissoSublusus=sublusus ;
#define LUNGHEZZA_PREF_SUBLUSUS 9
char *prefissoCultivar=c.v. ;
#define LUNGHEZZA_PREF_CV 5
char *suffissoProParte=p.p. ;
#define LUNGHEZZA_POST_PP 5
#define TEST_IBRIDO(n)
{if(strcmp(ibrido,(n))==0){buffer[caratteriInseriti]='x';buffer[caratteriInseriti+1]='
';caratteriInseriti+=2;}}
#define INSERISCI_PARTE(parte)
{memcpy(buffer+caratteriInseriti,VARDATA(parte),VARSIZE(parte)-VARHDRSZ);caratteriInseriti+=VARSIZE(parte)-VARHDRSZ+1;buffer[caratteriInseriti-1]='
';}
// posizione degli ibridi
#define IBRIDO_GENERE genus
#define IBRIDO_SPECIE specie
#define IBRIDO_SOTTOSPECIE subspecie
#define IBRIDO_VARIETA variety
#define IBRIDO_SOTTOVARIETA subvariety
#define IBRIDO_FORMA form
#define IBRIDO_RACE race
#define IBRIDO_SUBLUSUS sublusus
#define IBRIDO_CULTIVAR cultivar
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
char buffer[300];
int caratteriInseriti=0; // tiene il conto dei caratteri presenti in
buffer
Datum datumIbrido= PG_GETARG_DATUM(0);
bool proParte= PG_GETARG_BOOL(1);
text *genere = (PG_ARGISNULL( 2) || VARSIZE(PG_GETARG_TEXT_P(
2))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(2));
text *specieNome = (PG_ARGISNULL( 3) ||
VARSIZE(PG_GETARG_TEXT_P( 3))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(3));
text *specieAutore = (PG_ARGISNULL( 4) ||
VARSIZE(PG_GETARG_TEXT_P( 4))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(4));
text *sottospecieNome= (PG_ARGISNULL( 5) ||
VARSIZE(PG_GETARG_TEXT_P( 5))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(5));
text *sottospecieAutore = (PG_ARGISNULL( 6) ||
VARSIZE(PG_GETARG_TEXT_P( 6))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(6));
text *varietaNome= (PG_ARGISNULL( 7) ||
VARSIZE(PG_GETARG_TEXT_P( 7))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(7));
text *varietaAutore = (PG_ARGISNULL( 8) ||
VARSIZE(PG_GETARG_TEXT_P( 8))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(8));
text *sottoVarietaNome = (PG_ARGISNULL( 9) ||
VARSIZE(PG_GETARG_TEXT_P( 9))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(9));
text *sottoVarietaAutore = (PG_ARGISNULL(10) ||
VARSIZE(PG_GETARG_TEXT_P(10))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(10));
text *formaNome = (PG_ARGISNULL(11) ||
VARSIZE(PG_GETARG_TEXT_P(11))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(11));
text *formaAutore= (PG_ARGISNULL(12) ||
VARSIZE(PG_GETARG_TEXT_P(12))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(12));
text *raceNome = (PG_ARGISNULL(13) ||
VARSIZE(PG_GETARG_TEXT_P(13))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(13));
text *raceAutore = (PG_ARGISNULL(14) ||
VARSIZE(PG_GETARG_TEXT_P(14))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(14));
text *sublususNome = (PG_ARGISNULL(15) ||
VARSIZE(PG_GETARG_TEXT_P(15))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(15));
text *sublususAutore = (PG_ARGISNULL(16) ||
VARSIZE(PG_GETARG_TEXT_P(16))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(16));
text *cultivar = (PG_ARGISNULL(17) ||
VARSIZE(PG_GETARG_TEXT_P(17))==VARHDRSZ ?NULL:PG_GETARG_TEXT_P(17));
text
Re: [GENERAL] Remote Connection
Bob Pawley wrote: In pg_hba there is a reference to reloading postmaster using pg_ctl. Does postmaster reload when the server is restarted?? depending on your OS, there's a variety of ways of forcing the postmaster to reload or restart some changes require a restart, like LISTEN_ADDRESS, others just a reload (pg_hba.conf changes), so for a restart, just replace the word reload with restart in the following... RHEL, Fedora, CentOS... # service postgresql reload other SysV init based systems... # /etc/init.d/postgresql reload Solaris 10, using SMF... # svcadm refresh svc:/application/database/postgresql:version_82_64bit (or restart instead of refresh, and the service name will vary per the version) most Unix systems without a OS specific service manager script... # su - postgres -c pg_ctl reload -D /path/to/pg/data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't figure string compare result (using also custom C function)
On Fri, Feb 13, 2009 at 10:31:49PM +0100, Edoardo Panfili wrote: SELECT idSpecie,nome FROM specienomi WHERE idspecie=37026 and nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+- 37026 | X Agropogon littoralis (Sm.) C.E. Hubb. The same query but without one condition: no results. SELECT idSpecie,nome FROM specienomi WHERE nome='X Agropogon littoralis (Sm.) C.E. Hubb.'; idspecie | nome --+-- (0 rows) I can't figure why, can someone tell me how investigate? I've just looked back in the archives and noticed that you were asking about functional indexes; you do know that if you change the definition of a function that PG doesn't know to rebuild the index don't you? That would exhibit the symptoms you're seeing; i.e. the first case is using an index on idspecie and the second is using the (out-of-date) functional index. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remote Connection
On Fri, Feb 13, 2009 at 3:43 PM, Bob Pawley rjpaw...@shaw.ca wrote: In pg_hba there is a reference to reloading postmaster using pg_ctl. Does postmaster reload when the server is restarted?? Yep. restart shuts down pgsql, and then starts it up fresh, so it has to read its config files etc. Reload is useful because it doesn't shut down a server, which can be handy on 24/7 machines that need config changes. As noted by others, some changes require a restart, basically the things the server can't change while it's running, like share_buffers, which are allocated at start up. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
