Re: [GENERAL] plpgsql and insert
Depending on what client side library you are using you could use the RETURNING clause, see the docs: http://www.postgresql.org/docs/8.2/static/sql-insert.html Regards, Ben "Jamie Deppeler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > Have a quick question is possible to record a primary from a insert > stament > > eg > > xprimary := insert into schema.table(.,.,.,.) VALUES (); > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPGRADATION TO 8.1
So what's he meant to do? Write a longer question just so the mandatory disclamer that his company attaches to his e-mail takes up a lower percentage of his e-mail? (or should he not ask the question at all?) Regards, Ben > > Btw, I personally find a payload/noise ratio of 1/6 atrocious, > and not very kind regarding the mailing-list. > > ---(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: [GENERAL] Webappication and PostgreSQL login roles
You could originally connect to the database as some kind of power user. Check the password against the pg_shadow view (you would need to md5 your password somehow) and then do a SET SESSION AUTHORIZATION (or SET ROLE) to change your permissions. Not sure how secure this would be but it's the way I would try. Regards, Ben "Thorsten Kraus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > thanks for your answer. I cant use the username/password in my DSN because > I don't connect directly via JDBC to the database. I use hibernate for all > database actions. The username and password has to be stored in the > hibernate configuration file... > > Bye, > Thorsten > > > Lutz Broedel schrieb: >> >> Can you not use the username/password as part of the DSN? >> >> Regards, >> Lutz Broedel >> > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Webappication and PostgreSQL login roles
I think it's something like SELECT 'md5' + md5(password + username); Regards, Ben "Thorsten Kraus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] This would be a possible way. Now the question is which algorithm implementation of md5 PostgreSQL uses... Bye, Thorsten Ben Trewern schrieb: You could originally connect to the database as some kind of power user. Check the password against the pg_shadow view (you would need to md5 your password somehow) and then do a SET SESSION AUTHORIZATION (or SET ROLE) to change your permissions. Not sure how secure this would be but it's the way I would try. Regards, Ben "Thorsten Kraus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hi, thanks for your answer. I cant use the username/password in my DSN because I don't connect directly via JDBC to the database. I use hibernate for all database actions. The username and password has to be stored in the hibernate configuration file... Bye, Thorsten Lutz Broedel schrieb: Can you not use the username/password as part of the DSN? Regards, Lutz Broedel ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] role passwords and md5()
Looks like the password gets cleared when you rename a role.
Regards,
Ben
"Ben Trewern" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I thought I read this be for I sent it. :-(
>
> What I meant to say was:
> Does the password hash change (and how?) Or is the original username kept
> somewhere is the system tables?
>
> Regards,
>
> Ben
>
> "Ben Trewern" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>> How does this work when you rename a role? Does the is the password hash
>> changed (and how?) or is the original username kept somewhere in the
>> system tables?
>>
>> Regards,
>>
>> Ben
>>
>> "Andrew Kroeger" <[EMAIL PROTECTED]> wrote in message
>> news:[EMAIL PROTECTED]
>>> Lutz Broedel wrote:
>>>> Dear list,
>>>>
>>>> I am trying to verify the password given by a user against the system
>>>> catalog. Since I need the password hash later on, I can not just use
>>>> the
>>>> authentication mechanism for verification, but need to do this in SQL
>>>> statements.
>>>> Unfortunately, even if I set passwords to use MD5 encryption in
>>>> pg_hba.conf, the SQL function MD5() returns a different hash.
>>>>
>>>> A (shortened) example:
>>>> CREATE ROLE my_user WITH ENCRYPTED PASSWORD 'my_password';
>>>>
>>>> SELECT * FROM pg_authid
>>>> WHERE rolname='my_user' AND rolpassword=MD5('my_password');
>>>>
>>>> Any ideas, what to do to make this work?
>>>> Best regards,
>>>> Lutz Broedel
>>>
>>> A quick look at the source shows that the hashed value stored in
>>> pg_authid uses the role name as a salt for the hashing of the password.
>>> Moreover, the value in pg_authid has the string "md5" prepended to the
>>> hash value (I imagine to allow different hash algorithms to be used, but
>>> I haven't personally seen anything but "md5").
>>>
>>> Given your example above, the following statement should do what you are
>>> looking for:
>>>
>>> SELECT * FROM pg_authid WHERE rolname='my_user' AND rolpassword = 'md5'
>>> || md5('my_password' || 'my_user');
>>>
>>> Hope this helps.
>>>
>>> Andrew
>>>
>>> ---(end of broadcast)---
>>> TIP 5: don't forget to increase your free space map settings
>>>
>>
>>
>
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Providing user based previleges to Postgres DB
Providing user based previleges to Postgres DBSee: http://www.postgresql.org/docs/8.2/interactive/user-manag.html Regards, Ben <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hi All, Currently in one of the projects we want to restrict the unauthorized users to the Postgres DB. Here we are using Postgres version 8.2.0 Can anybody tell me how can I provide the user based previleges to the Postgres DB so that, we can restrict the unauthorized users as well as porivde the access control to the users based on the set previleges by the administrator. Thanks and Regards, Ramac The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: [GENERAL] role passwords and md5()
How does this work when you rename a role? Does the is the password hash
changed (and how?) or is the original username kept somewhere in the system
tables?
Regards,
Ben
"Andrew Kroeger" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Lutz Broedel wrote:
>> Dear list,
>>
>> I am trying to verify the password given by a user against the system
>> catalog. Since I need the password hash later on, I can not just use the
>> authentication mechanism for verification, but need to do this in SQL
>> statements.
>> Unfortunately, even if I set passwords to use MD5 encryption in
>> pg_hba.conf, the SQL function MD5() returns a different hash.
>>
>> A (shortened) example:
>> CREATE ROLE my_user WITH ENCRYPTED PASSWORD 'my_password';
>>
>> SELECT * FROM pg_authid
>> WHERE rolname='my_user' AND rolpassword=MD5('my_password');
>>
>> Any ideas, what to do to make this work?
>> Best regards,
>> Lutz Broedel
>
> A quick look at the source shows that the hashed value stored in
> pg_authid uses the role name as a salt for the hashing of the password.
> Moreover, the value in pg_authid has the string "md5" prepended to the
> hash value (I imagine to allow different hash algorithms to be used, but
> I haven't personally seen anything but "md5").
>
> Given your example above, the following statement should do what you are
> looking for:
>
> SELECT * FROM pg_authid WHERE rolname='my_user' AND rolpassword = 'md5'
> || md5('my_password' || 'my_user');
>
> Hope this helps.
>
> Andrew
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
---(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: [GENERAL] role passwords and md5()
I thought I read this be for I sent it. :-(
What I meant to say was:
Does the password hash change (and how?) Or is the original username kept
somewhere is the system tables?
Regards,
Ben
"Ben Trewern" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> How does this work when you rename a role? Does the is the password hash
> changed (and how?) or is the original username kept somewhere in the
> system tables?
>
> Regards,
>
> Ben
>
> "Andrew Kroeger" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>> Lutz Broedel wrote:
>>> Dear list,
>>>
>>> I am trying to verify the password given by a user against the system
>>> catalog. Since I need the password hash later on, I can not just use the
>>> authentication mechanism for verification, but need to do this in SQL
>>> statements.
>>> Unfortunately, even if I set passwords to use MD5 encryption in
>>> pg_hba.conf, the SQL function MD5() returns a different hash.
>>>
>>> A (shortened) example:
>>> CREATE ROLE my_user WITH ENCRYPTED PASSWORD 'my_password';
>>>
>>> SELECT * FROM pg_authid
>>> WHERE rolname='my_user' AND rolpassword=MD5('my_password');
>>>
>>> Any ideas, what to do to make this work?
>>> Best regards,
>>> Lutz Broedel
>>
>> A quick look at the source shows that the hashed value stored in
>> pg_authid uses the role name as a salt for the hashing of the password.
>> Moreover, the value in pg_authid has the string "md5" prepended to the
>> hash value (I imagine to allow different hash algorithms to be used, but
>> I haven't personally seen anything but "md5").
>>
>> Given your example above, the following statement should do what you are
>> looking for:
>>
>> SELECT * FROM pg_authid WHERE rolname='my_user' AND rolpassword = 'md5'
>> || md5('my_password' || 'my_user');
>>
>> Hope this helps.
>>
>> Andrew
>>
>> ---(end of broadcast)---
>> TIP 5: don't forget to increase your free space map settings
>>
>
>
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] why postgresql over other RDBMS
Better support! Where else can you get feedback from the actual programmers (sometimes within minutes of writing a message) than here? Ben > Hi > I was wondering, apart from extensive procedural language support > and being free, > what are other major advantages of Postgresql over other major > RDBMS like oracle and sql server. > > Any pointers would be highly appreciated. > > Thanks, > ~Jas ---(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: [GENERAL] Database Security
Look at changing your pg_hba.conf file If you have a line in the file like: hostall all 127.0.0.1/32 trust change it to: hostall all 127.0.0.1/32 md5 then run: pg_ctl reload should get you whare you want to be. Ben "Danilo Freitas da Costa" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi all!!! > > I'm using Postgres at company I work for few time. > I already tried many ways to create a security for my database but not > sucessfull. > The postgres was installed with default configuration and I had definied > "postgres" as root user. > However, someone else user I create have full access on all databases in > my server, with some limitations. > How can I configure access level to a database? How can I force every user > type your password to access the database? > > Thanks, > Danilo > > ---(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 > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Alternative to knoda, kexi and rekall?
For administration try pgAdmin III but to make applications you could try Gambas see: http://gambas.sourceforge.net/ or even Lazarus see: http://www.lazarus.freepascal.org/ For internet stuff try Ruby on Rails. It has a bit of a steep learning curve to start with but it's a RAD tool when you get the hang of it. Ben BTW whats the problem with Rekall? I thought it could just use QT. "Michelle Konzack" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello, > > I am using PostgreSQL since more then 6 years now and for 1 1/2 years > "rekall". Now there is a problem with the crapy QT and I have no > Frontend anymore which works WITHOUT (!!!) KDE or GNOME which I hate! > > Currently I am using "pgAccess" to check my PostgreSQL but it is very > limited. > > Does anyone know a Frontend for PostgreSQL which I can use to design > and admin a very huge Database (over 160 GByte and grown; the biggest > table is over 120 GByte) > > I need it urgentiel under plain/x without GNOME and KDE. > > If OSS is not availlable, a commercial product? > > I am not a PostgreSQL guru, but since I have lost last year my two > Iranien programmers, I am working alone and need support in form of > good Software. > > Please note, that I am using Debian GNU/Linux 3.0 and 3.1 and NO, I WILL > NOT SWITCH TO WINDOWS, EVEN THERE ARE VERY GOOD GUI'S FOR POSTGRESQL. > > I wish, such GUI's exist under Linux! > > Greetings >Michelle Konzack >Systemadministrator >Tamay Dogan Network >Debian GNU/Linux Consultant > > > -- > Linux-User #280138 with the Linux Counter, http://counter.li.org/ > # Debian GNU/Linux Consultant # > Michelle Konzack Apt. 917 ICQ #328449886 > 50, rue de Soultz MSM LinuxMichi > 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
PG Lightning does Code Completion. I don't think there is a frontend tool that can step through a PL/pgSQL function. Ben ""Ken Winter"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Is a better PL/pgSQL editor / debugger than pgAdmin III or phpPgAdmin available anywhere? I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near "LOOP" CONTEXT: compile of PL/pgSQL function "gen_history" near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. (On the positive side, pgAdmin IIIs use of different colors to distinguish different program elements [variables, keywords, string constants, comments] made the staring part easier to do.) Ive learned that pgAdmin syntax error can mean anything from a missing ; to a faulty block structure to an undeclared variable to I dont know what else, and as witness the example that error may be nowhere near the line that is flagged. So Im looking for a PL/pgSQL tool that would at least provide more diagnostic error messages. Preferably, it would also offer some sort of breakpoint function to let the developer see the values of variables at specified points in the code. I looked on the pgAdmin web site. The only place a PL/pgSQL debugger was mentioned was on the to do page, under major projects (http://www.pgadmin.org/development/todo.php), which I guess means dont hold your breath. As editors, the two pgAdmin tools apparently dont offer elementary functions such as find and replace, which means I have to slurp my code out into a text editor when I really need these things. So I guess my questions are: Is it the case that the pgAdmin tools actually do offer these features, but I just havent found them yet? If so, can you show me where they are? Are these functions available through add-ons to either pgAdmin tool? If so, where can I get these add-ons? Are there other PL/pgSQL editors that provide these functions? If so, what? Obviously, Id prefer a free one, but would pay for one if necessary. ~ TIA ~ Ken
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Try SELECT timeofday()::TIMESTAMP; Regards, Ben ""Christopher J. Bottaro"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > I understand that CURRENT_TIMESTAMP marks the beginning of the current > transaction. I want it to be the actual time. How do I do this? > timeofday() returns a string, how do I convert that into a TIMESTAMP? > > Is it possible to create a column with DEFAULT value evaluated to the > actual > current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the > current transaction). > > What I do now to get it to work is do a COMMIT right before the insert, > that > way CURRENT_TIMESTAMP is (pretty close to) the actual time. ...but that > is > so crappy and doesn't work if I actually need to use transactional > features > (i.e. rollback). > > Thanks for the help, > -- C > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Binary or compiled version?
I haven't had any joy trying to install the Redhat RPMs on mandrake 10.1. It might be me but I did take some time trying. I also tried using the SRPMs and building my own but that didn't work either. Since then I've compiled my version and it works great. The only thing I needed to do was mess around with the startup scripts a bit. Regards, Ben "Jaqui Greenlees" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Alejandro D. Burne wrote: >> Hi, I'm installing 8.0.2 on Mandrake and I saw binarys rpms only for RH. >> Can someone tell me if is better install a rpm version or compile from >> source in this case? >> >> Thanks, Alejandro >> > > the rpm should work fine, even though it's rh. > if not, rpmdrake ( urpmi ) will remove 8.0.2 so building from sources with > clean system will be possible. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Delphi personal (was Playing with PostgreSQL and Access VBA)
I'm pretty sure that zeosdbo needs a version of Delphi with TDataset support. I don't think that the Personal editions have that. You can use the direct access parts of Zeos with the personal editions but then you might as well use Free Pascal and Lazarus as they have just ported ZeosDbo see http://www.lazarus.freepascal.org/. Ben "Tony Caduto" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > All you need is the Personal Edition of say Delphi 6 or Delphi 7, and even > Delphi 2005. > For Delphi 2005 personal edition see this link: > http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake > Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005 > can do, and I bet you can find version 5 on ebay or elseware for dirt > cheap. > > The personal editions do not include database access components by > themselves, but you certainly can use third party tools such as Zeos > (http://www.zeoslib.net) or those from > http://www.microolap.com. > You could even grab a copy of libpq.pas if you want to do direct access to > PG. > > You get what you pay for and there is nothing available for Python or > WXwidgets that even comes close to Delphi for RAD/GUI database > development. > Not to mention all the other stuff you can do with Delphi, it's also a > great programming language for console apps, services, TCP/IP servers. > There are even remote control applications built with it ala PC Anywhere. > > > Plus if you call borland, I am sure they would give you the upgrade price > if you have a old copy of VB laying around for a competive upgrade. > >>> I was very impressed by what one member said regarding Delphi, but, >>> when I looked at pricing,... well I would have to lie through my teeth >>> to get the cheap academic version, and the personal version sounds like >>> it doesnt have the file access abilities for PostgreSQL. And the prices >>> for enterprise versions at programmersparadise.com like $4000, sort of >>> puts me off. >> >> > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Now() function
BTW in Postgresql 8.0 you can do:
ALTER TABLE foo ALTER foo_timestamp TYPE timestamp(0) with timezone;
It'll do the truncation for you.
Regards,
Ben
"Michael Glaesemann" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> On Jun 10, 2005, at 11:37 AM, Michael Glaesemann wrote:
>
>> A short term solution would be to update the column using something like
>> update foo set foo_timestamp = date_trunc(foo_timestamp).
>
> Sorry. That isn't clear (or correct!) Complete example at the bottom of
> the email.
>
> UPDATE foo
> SET foo_timestamp = date_trunc('second',foo_timestamp);
>
>
>> http://www.postgresql.org/docs/7.4/interactive/functions-
>> datetime.html#FUNCTIONS-DATETIME-TRUNC
>
> Sorry for any confusion.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> test=# create table foo (foo_id serial not null unique, foo_timestamp
> timestamptz not null) without oids;
> NOTICE: CREATE TABLE will create implicit sequence "foo_foo_id_seq" for
> serial column "foo.foo_id"
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> "foo_foo_id_key" for table "foo"
> CREATE TABLE
> test=# insert into foo (foo_timestamp) values (current_timestamp);
> INSERT 0 1
> test=# insert into foo (foo_timestamp) values (current_timestamp);
> INSERT 0 1
> test=# insert into foo (foo_timestamp) values (current_timestamp);
> INSERT 0 1
> test=# insert into foo (foo_timestamp) values (current_timestamp);
> INSERT 0 1
> test=# select * from foo;
> foo_id | foo_timestamp
> +---
> 1 | 2005-06-10 11:55:48.459675+09
> 2 | 2005-06-10 11:55:49.363353+09
> 3 | 2005-06-10 11:55:49.951119+09
> 4 | 2005-06-10 11:55:50.771325+09
> (4 rows)
>
> test=# update foo set foo_timestamp = date_trunc ('second',foo_timestamp);
> UPDATE 4
> test=# select * from foo;
> foo_id | foo_timestamp
> +
> 1 | 2005-06-10 11:55:48+09
> 2 | 2005-06-10 11:55:49+09
> 3 | 2005-06-10 11:55:49+09
> 4 | 2005-06-10 11:55:50+09
> (4 rows)
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] When is Like different to =
All, I've a query: SELECT c.code, c.pc_no, jl.event_no, jl.order_number, jl.location, s.status, cs.commercial_status FROM ((codes as c JOIN job_list as jl ON c.id = jl.code_id) JOIN status as s ON jl.event_no = s.event_no) JOIN commercial_status AS cs on jl.event_no = cs.event_no WHERE (status = 'Job Allocated') AND (code_id = 39); Where codes and job_list are tables and status is a view: CREATE VIEW status AS SELECT job_list.event_no, status(job_list.event_no) AS status FROM job_list JOIN user_codes ON job_list.code_id = user_codes.code_id WHERE user_codes.user_name::name = "current_user"(); CREATE FUNCTION status(int4) RETURNS text AS $BODY$SELECT CASE WHEN job_cancelled IS NOT NULL THEN 'Cancelled'::text WHEN job_sheet_received IS NOT NULL THEN 'Job Sheet Received'::text WHEN works_complete IS NOT NULL THEN 'Works Complete'::text WHEN awaiting_action_id IS NOT NULL THEN 'Awaiting Action'::text WHEN attend_date IS NOT NULL THEN 'Job Attended'::text WHEN issued_to IS NOT NULL THEN 'Job Allocated'::text ELSE 'Not Allocated'::text END FROM job_list WHERE event_no = $1$BODY$ LANGUAGE 'sql' STABLE; The above query should return one row from my current database but does not. If I change the where clause from (status = 'Job Allocated') AND (code_id = 39) to (status LIKE 'Job Allocated') AND (code_id = 39) it does return the row. What am I missing? Regards, Ben ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] When is Like different to =
After some more digging I found there was an index: CREATE INDEX job_list_status_idx ON job_list USING btree (status(event_no)); I had previously created. I must have changed the function from IMMUTABLE to STABLE after creating the index or I assume I wouldn't have been able to create the index. When I dropped the index Like and = started working correctly. BTW should there be check so an error is thrown if I try to change a function used in an index from IMMUTABLE to STABLE? Ben "Ben Trewern" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > All, > > I've a query: > > SELECT >c.code, c.pc_no, jl.event_no, jl.order_number, jl.location, s.status, > cs.commercial_status > FROM >((codes as c JOIN job_list as jl ON c.id = jl.code_id) JOIN status as s > ON jl.event_no = s.event_no) >JOIN commercial_status AS cs on jl.event_no = cs.event_no > WHERE >(status = 'Job Allocated') AND (code_id = 39); > > Where codes and job_list are tables and status is a view: > > CREATE VIEW status AS > SELECT job_list.event_no, status(job_list.event_no) AS status > FROM job_list > JOIN user_codes ON job_list.code_id = user_codes.code_id > WHERE user_codes.user_name::name = "current_user"(); > > CREATE FUNCTION status(int4) > RETURNS text AS > $BODY$SELECT > CASE >WHEN job_cancelled IS NOT NULL THEN 'Cancelled'::text >WHEN job_sheet_received IS NOT NULL THEN 'Job Sheet > Received'::text >WHEN works_complete IS NOT NULL THEN 'Works Complete'::text >WHEN awaiting_action_id IS NOT NULL THEN 'Awaiting > Action'::text >WHEN attend_date IS NOT NULL THEN 'Job Attended'::text >WHEN issued_to IS NOT NULL THEN 'Job Allocated'::text >ELSE 'Not Allocated'::text > END > FROM > job_list > WHERE > event_no = $1$BODY$ > LANGUAGE 'sql' STABLE; > > The above query should return one row from my current database but does > not. If I change the where clause from (status = 'Job Allocated') AND > (code_id = 39) > to > (status LIKE 'Job Allocated') AND (code_id = 39) > it does return the row. > > What am I missing? > > Regards, > > Ben > ---(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: [GENERAL] PostgreSQL, Lazarus and zeos ?
You need the cvs version of zeoslib to work with Lazarus. It's also the 6.5.something version. The old 5.x only worked with Delphi. See the Lazarus forums for more information. Ben >""Zlatko Matiæ"" <[EMAIL PROTECTED]> wrote in message >news:[EMAIL PROTECTED] >Hi. >Someone mentioned Lazarus as good IDE for working with PostgreSQL, so >that's the reason I started to learn Lazarus... >Now, I was told that I need to install ZEOS library in order to work with >PostgreSQL. >I downloaded the following .zip files: zeosdbo-5.0.7-beta, >zeosctrl-1.0.0-beta. I have installed Lazarus 0.9.8 (binaries) on WIndows >XP. >It seems that these zeos files are intended to be for Delphi, not for >Lazarus ? >What am I supposed to do now ? How to install it ? >Is it really neccessary to instal Zeos in order to work with PostgreSQL? >Sorry for stupid questions, but this is totaly new stuff for me... >Thanks in advance, >Zlatko ---(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: [GENERAL] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > I don't care if it's part of the SQL standard or not. I don't care if > oracle does it or not. You're losing mysql converts as they go > through the tutorial and get to this point. Or worse, they just "grant > all" because it's easier, thus causing security holes. User > friendliness matters. > You can use the pgAdmin's grant wizard to do what you want. Regards, Ben BTW thanks for the polite e-mail. :-/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] usage of indexes for inner joins
Sequence scans of an empty table are going to be faster than an index scan,
so the database uses the sequence scan. Put some data in the tables (some
thousands or millions of records) and then see if it uses an index scan.
Ben
""Jan Theodore Galkowski"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I fear this has been asked many times about PostgreSQL, and I have read
> the docs about how indexes are supposed to be defined and used, but I
> don't understand why the engine and optimizer is doing what it does in
> the simplest of situations. Is it that its tuning is heavily data
> dependent?
>
> My case of interest is more complicated, but I decided to create a toy
> case to try to understand. Here it is:
>
>
> -- Table "foo" DDL
>
> CREATE TABLE "public"."foo"(
>
> "projectid" int4 NOT NULL ,
>
> "uid" int4 NOT NULL ,
>
> "name" varchar(254) NOT NULL ,
>
> "ver" varchar(127) NOT NULL ,
>
> "startdate" date NOT NULL ,
>
> "enddate" date NOT NULL ,
>
> "status" varchar(254) NOT NULL ,
>
> "percentdone" numeric(7,2) NOT NULL ,
>
> "championuid" int4 NOT NULL ,
>
> "pmuid" int4 NOT NULL ,
>
> PRIMARY KEY ("projectid")
>
> ) WITHOUT OIDS;
>
>
> -- Table "bignum" DDL
>
> CREATE TABLE "public"."bignum"(
>
> "thing" numeric(100) NOT NULL
>
> ) WITHOUT OIDS;
>
> CREATE INDEX "t" ON "public"."bignum" USING btree ("thing");
>
>
> Running
>
>EXPLAIN ANALYZE SELECT A.* FROM bignum B, foo A WHERE A.projectid
>= B.thing;
>
> yields:
>
>Nested Loop (cost=0.00..15.51 rows=1 width=407) (actual
>time=0.041..0.041 rows=0 loops=1)
>
> Join Filter: ((a.projectid)::numeric = b.thing) ->
>
>Seq Scan on bignum b (cost=0.00..1.01 rows=1 width=16) (actual
>time=0.024..0.027 rows=1 loops=1) ->
>
>Seq Scan on foo a (cost=0.00..11.80 rows=180 width=407) (actual
>time=0.003..0.003 rows=0 loops=1)
>
>Total runtime: .169 ms ;
>
> Like *how* *come*? There are indexes on both columns of the join. Is
> it the NUMERIC datatype messing things up? Unlikely, as I've seen the
> same with INTEGERs.
>
> If it is data dependent (these tables are presently empty), any
> suggestions as to how to tune a database for unknown mixes of data?
>
> This is run on the Windows version of PG, but I'm seeing the same kind
> of thing on Linux.
>
> Thanks.
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Fwd: Problem installing Postgresql on MDK10.0
I think you have to install ncurses. On Mdk 10 its libncurses5-devel I think. Try that and see what happens. Regards, Ben ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Pg 8.0rc5 to 8.0.1 update
Hi, Just a quick question. Do I need to do an initdb to upgrade a cluster from v8.0rc5 to v8.0.1 or can I just do a make install. TIA Ben ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] to_char bug?
Is there any reason why : SELECT char_length(to_char(1, '000')); Gives a result char_length - 4 (1 row) It seems that to_char(1, '000') gives a string " 001" with a space in front. Is this a bug? Regards, Ben ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] to_char bug?
>From the docs: "FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width" It works now but for one I don't understand why the space is added in the firs place and two I think the docs don't tell the whole story ie leading blanks and I assume trailing zeros if applicable. Regards, Ben "Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Ben Trewern" <[EMAIL PROTECTED]> writes: >> It seems that to_char(1, '000') gives a string " 001" with a space in >> front. >> Is this a bug? > > No. > > Possibly you want 'FM000'. > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] to_char bug?
Thanks, sometimes the obvious just passes me by. :-( >If the number is negative there needs to be room for the minus sign... "Martijn van Oosterhout" wrote in message news:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] pgFoundry.org not working!
I'm currently getting: "PgFoundry Could Not Connect to Database" when I try to visit http://pgfoundry.org Regards, Ben ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgFoundry.org not working!
Working again now! Regards, Ben "Ben Trewern" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm currently getting: > > "PgFoundry Could Not Connect to Database" > > when I try to visit http://pgfoundry.org > > Regards, > > Ben > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?
The solution you need all depends on the problem you are having. If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer! Regards, Ben "Najib Abi Fadel" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Robin Ericsson <[EMAIL PROTECTED]> wrote: On 9/18/06, Najib Abi Fadel wrote: > Hi, > > i was searching for a load balancing solution for > postgres, I found some ready to use software like > PGCluster, Slony, pgpool and others. > > It would really be nice if someone knows which one is > the best taking in consideration that i have an > already running application that i need to load > balance. There isn't one tool that is the best, all three work very good based on where they are used and what they are used for. -- regards, Robin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Did you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application. Thanks in advance for any help. Najib. How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(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: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?
You can try using pg_pconnect instead of pg_connect. It has some downsides so see the docs. Also - check your memory usage, it may be you could fix this by reducing work_mem or similar. Regards, Ben "Najib Abi Fadel" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]...Hi again,How can i use connection pooling ? Should i use a software like PGPool ? Will the connection pooling boost considerably the performance ?Leonel adviced me to use persistent connections ? hos do i use that ?PS: I am using PHP for my applications.ThanksNajibTalha Khan <[EMAIL PROTECTED]> wrote: You should also consider using connection pooling inorder to attain better performance. Regards Talha Khan On 9/20/06, Najib Abi Fadel <[EMAIL PROTECTED]> wrote: I have a web application that is accessed by a large number of users. My application is written in PHP and uses postgres. Apache is our web server.The performance of my application drops down when a large numbers of users connects at the same time. I need to have a better response time ! That's why i need to load balance the web requests and the database. Regards,Najib.Ben Trewern <[EMAIL PROTECTED]> wrote: The solution you need all depends on the problem you are having. If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer!Regards,Ben "Najib Abi Fadel" wrote in message news:[EMAIL PROTECTED] Robin Ericsson wrote:On 9/18/06, Najib Abi Fadel wrote:> Hi,>> i was searching for a load balancing solution for> postgres, I found some ready to use software like > PGCluster, Slony, pgpool and others.>> It would really be nice if someone knows which one is> the best taking in consideration that i have an> already running application that i need to load > balance.There isn't one tool that is the best, all three work very good basedon where they are used and what they are used for.-- regards,Robin---(end of broadcast)--- TIP 5: don't forget to increase your free space map settingsDid you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application.Thanks in advance for any help.Najib.How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. Do you Yahoo!?Next-gen email? Have it all with the all-new Yahoo! Mail.
Re: [GENERAL] sudden drop in delete performance
Did you 'vacuum analyze' after you did the update? Make sure you have the correct indexes in place on your foreign keys. Did you have fsync off on your previous installation? Give some more details and I'm sure people will be able to give better advice than me. Regards, Ben ""surabhi.ahuja"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Nothing else has changed. Please help thanks regards Surabhi
Re: [GENERAL] Connecting
I'd try zeoslib (http://forum.zeoslib.net.ms/ or http://sourceforge.net/projects/zeoslib/) instead of ODBC. The 6.1.5 version (with patches) works with Delphi 4 and always worked well for me. Regards, Ben "Bob Pawley" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... I'm appealing for help from this list as the ODBC list has few people and no answers. I have built a database in Postgresql version 8.0. I want to connect it to Delphi version 4. I have attempted to connect using the Postgresql ODBC with no success. Is what I am attempting to do possible considering that Postrgresql version is a lot newer than Delphi? If it is possible, can someone point me to a tutorial that can guide me through the steps. I am new to interspecies connections? Is there a better method of making this comnnection? Bob Pawley
Re: [GENERAL] could not create shared memory segment in Windows XP
Have you got Cygwin installed? I had similar problems due to Cygwin being eariler in my PATH than Pg. Regards, Ben "Andrus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm unable to create database cluster in Windows 2000 server. > > initdb returns error > > FATAL: could not create shared memory segment: No such file or directory > > any idea how to create cluster in Windows 2000 ? > > > Task MAnager shows physical memory: > > Total: 392664 > Available: 81860 > System Cache: 157xxx > > > If available physical memory is too low, how to increate it (decrease > system cache) ? > > Andrus. > > > C:\pgsql>bin\initdb -E=UTF8 -d -D data > Running in debug mode. > VERSION=8.1.3 > PGDATA=data > share_path=C:/pgsql/share > PGPATH=C:/pgsql/bin > POSTGRES_SUPERUSERNAME=postgres > POSTGRES_BKI=C:/pgsql/share/postgres.bki > POSTGRES_DESCR=C:/pgsql/share/postgres.description > POSTGRESQL_CONF_SAMPLE=C:/pgsql/share/postgresql.conf.sample > PG_HBA_SAMPLE=C:/pgsql/share/pg_hba.conf.sample > PG_IDENT_SAMPLE=C:/pgsql/share/pg_ident.conf.sample > The files belonging to this database system will be owned by user > "postgres". > This user must also own the server process. > > The database cluster will be initialized with locale > Estonian_Estonia.1257. > > creating directory data ... ok > creating directory data/global ... ok > creating directory data/pg_xlog ... ok > creating directory data/pg_xlog/archive_status ... ok > creating directory data/pg_clog ... ok > creating directory data/pg_subtrans ... ok > creating directory data/pg_twophase ... ok > creating directory data/pg_multixact/members ... ok > creating directory data/pg_multixact/offsets ... ok > creating directory data/base ... ok > creating directory data/base/1 ... ok > creating directory data/pg_tblspc ... ok > selecting default max_connections ... 10 > selecting default shared_buffers ... 50 > creating configuration files ... ok > creating template1 database in data/base/1 ... DEBUG: TZ > "Europe/Helsinki" > matc > hes Windows timezone "FLE Daylight Time" > DEBUG: invoking IpcMemoryCreate(size=1327104) > DEBUG: mapped win32 error code 161 to 2 > FATAL: could not create shared memory segment: No such file or directory > DETAIL: Failed system call was shmget(key=1, size=1327104, 03600). > DEBUG: proc_exit(1) > DEBUG: shmem_exit(1) > DEBUG: exit(1) > child process was terminated by signal 1 > initdb: removing data directory "data" > > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] No password prompt logging into Postgres 8.4
Using th new postgresql 8.4.0 (compiled) On Ubuntu 8.10 I did an initdb, added a password to the postgres user and then changed the pg_hba.conf to: local all all md5 host all all 127.0.0.1/32 md5 Restarted Postgresql. If I log in normally: postg...@ben-desktop:~$ psql Password: psql (8.4.0) Type "help" for help. postgres=# But if i do : b...@ben-desktop:~$ psql -Upostgres psql (8.4.0) Type "help" for help. postgres=# You can also do : b...@ben-desktop:~$ psql -hlocalhost -Upostgres psql (8.4.0) Type "help" for help. postgres=# Note no password prompt either time! Looks like a bug or am I not understanding this properly. Ben -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump/pg_dumpall do not correctly dump search_path
All, There seems to be a bug in pg_dumpall: For one of my dbs I've done: ALTER DATABASE dbname SET search_path = mw, public; If I do a pg_dumpall I get a line like: ALTER DATABASE dbname SET search_path TO 'mw, public'; note the 's. It's also in a place in the dump before the mw schema is created. It's not a big problem but it makes dumps less automatic. BTW If I do a pg_dump dbname I get a dump which does not reference the search_path change. I'm not sure if this is by design or it is just missing. I'm using PostgreSQL 7.4.5 on linux Thanks for any help. Ben _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
