Re: [GENERAL] What's size of your PostgreSQL Database?
On Fri, Aug 15, 2008 at 11:42 PM, Amber [EMAIL PROTECTED] wrote: Dear all: We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list: DB is ~650m rows across 10 tables and is currently around 160gb. Running on Ubuntu, mostly because this db started out as a toy and it was easy. It's done well enough thus far that it isn't worth the hassle to replace it with anything else. Currently only using Raid 0; the database can be regenerated from scratch if necessary so we don't have to worry overmuch about disk failures. Machine is a quad-core Xeon 2.5 with 4g of RAM. Our access pattern is a little odd; about half the database is wipe and regenerated at approximately 1-2 month intervals (the regeneration takes about 2 weeks); in between there's a nightly computation run that creates a small amount of new data in two of the tables. Both the regeneration and the addition of the new data depends very heavily on many, many several table joins that generally involve about 50% of the database at a time. We've been fairly pleased with the performance overall, though it's taken some tweaking to get individual operations to perform adequately. I can't speak to pure load operations; all of our bulk-load style ops are 4k-row COPY commands interspersed among a lot of big, complicated aggregate queries- not exactly ideal from a cache perspective. Concurrent readers are anywhere from 1-8, and we're not in a cluster. Sequential transfer rate is usually a touch over 100mb/sec; we don't have a lot of disks on this machine (though that may change oh how some of our index scans long for more spindles). The performance improvements made in the past few releases have been incredibly helpful- and very much noticeable each time. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to execute 'set session role' from plpgsql function?
Hi,everybody! I wish to have a function with code above, but compiller generate syntactic error at the line SET SESSION ROLE wishedrole;. How to pass the wishedrole value to the structure? CREATE OR REPLACE FUNCTION f_switch_role(INOUT text,INOUT boolean) AS $BODY$ DECLARE wishedrole ALIAS FOR $1; resetrole ALIAS FOR $2; BEGIN if resetrole=true then RESET ROLE; RETURN; end if; ERROR OCURS AT THE NEXT LINE SET SESSION ROLE wishedrole; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
On Aug 18, 9:23 pm, Lew [EMAIL PROTECTED] wrote: Dale wrote: Hi, I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? http://www.postgresql.org/docs/8.3/static/sql-update.html does not list an INTO clause for UPDATE, and when you think about it, indeed such a clause doesn't make sense. -- Lew http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html As per this documentation, you should be able to do it. It works for the INSERT command, but not UPDATE. For the INSERT command, it makes my code look neater and I image it's more efficient too. Dale. -- 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 get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale wrote: Hi, I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? http://www.postgresql.org/docs/8.3/static/sql-update.html does not list an INTO clause for UPDATE, and when you think about it, indeed such a clause doesn't make sense. -- Lew -- 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 get UPDATE ... RETURNING ... INTO ... to compile successfully
Hi, I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? Regards, Dale. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL on Windows x64
Hi, Does PostgreSQL can run on Windows x64? If yes, which version? Thanks, G -- 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] PostgreSQL on Windows x64
On Mon, Aug 18, 2008 at 7:48 AM, Goboxe [EMAIL PROTECTED] wrote: Hi, Does PostgreSQL can run on Windows x64? If yes, which version? All versions (though you should start with 8.3.3): http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Is_there_a_64-bit_build_of_PostgreSQL_for_Windows.3F -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NOT DEFERRABLE as default, why and how to manage it.
I just learnt that NOT DEFERRABLE is default. I vaguely understand that generally stricter policies protect distracted programmers from making mistakes... but missing an alter constraint it makes refactoring a PITA. Is it mandated by SQL standard? Any other rational reason to make NOT DEFERRABLE default? Is there any shortcut if I've to change to deferrable most of my constraints? Other than pgfoundry is there any other recipe repository where to look for refactoring tools for postgresql? -- 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] Pg dump Error
I have a problem with pg_dump on 2 computers. On the first computer, pg_dump runs very well, but on the second pg_dump has a error : pg_dump: reading triggers for table ph57 pg_dump: reading triggers for table phts pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: message contents do not agree with length in message type D message contents do not agree with length in message type c server sent data (D message) without prior row description (T message) pg_dump: The command was: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 pg_dump: *** aborted because of error Please help me. Thank you very much. Sorry for my English TuanHa
Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.
Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: I just learnt that NOT DEFERRABLE is default. Is it mandated by SQL standard? Yes. Is there any shortcut if I've to change to deferrable most of my constraints? Probably not, short of writing a little script. Other than pgfoundry is there any other recipe repository where to look for refactoring tools for postgresql? The wiki, I'd say. -- 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] NOT DEFERRABLE as default, why and how to manage it.
On Tue, 19 Aug 2008 11:20:08 +0300 Peter Eisentraut [EMAIL PROTECTED] wrote: Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: I just learnt that NOT DEFERRABLE is default. Is it mandated by SQL standard? Yes. Is there any reason they put it that way in the standard other than the mantra stricter is better? Is there any shortcut if I've to change to deferrable most of my constraints? Probably not, short of writing a little script. Reading the wiki an alter constraint is in the TODO. What about a: update pg_constraint set deeferrable=true where contype='f' and confupdtype'r' and confdeltype'r' ... BTW looking at pg_constraint and http://www.alberton.info/postgresql_meta_info.html was inspirational. What are the general rules about modifying the system tables? Where can I find what can be done and when and what can't be done? Other than pgfoundry is there any other recipe repository where to look for refactoring tools for postgresql? The wiki, I'd say. Thanks -- 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] 8.3.3 win32 crashing
Does this mean anything to anyone? Faulting application postgres.exe, version 8.3.3.8160, faulting module msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. I have a function that's doing a summary report counting data. It makes a couple of small temp tables then makes a big temp table. In the query that makes the big temp table, there's an immutable function that gets called thousands of times in the execution. In the immutable function, there was a raise notice for debugging . Without the raise, the query finishes in about 40sec. With it, the postgres.exe grinds down to about 2% cpu usage and eventually throws the message above. Then I reboot the machine - nothing responds to control. After reading of recent win32 network buffer problems, I'm wondering if this is another one. The context statement is nearly 8k long. The server is w2k3 on a 3.8ghz P4 with 3g memory on a 10mbit network (don't ask about the network - it just is and there's nothing I can do about it). The client is xp pentium M notebook, 2ghz, 1g memory. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] How to execute 'set session role' from plpgsql function?
Олег Василенко wrote: I wish to have a function with code above, but compiller generate syntactic error at the line SET SESSION ROLE wishedrole;. How to pass the wishedrole value to the structure? CREATE OR REPLACE FUNCTION f_switch_role(INOUT text,INOUT boolean) AS $BODY$ DECLARE wishedrole ALIAS FOR $1; resetrole ALIAS FOR $2; BEGIN if resetrole=true then RESET ROLE; RETURN; end if; ERROR OCURS AT THE NEXT LINE SET SESSION ROLE wishedrole; You need to use dynamic SQL, e.g. EXECUTE 'SET SESSION ROLE ' || lower(regexp_replace(wishedrole, '', '', 'g')) || ''; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE The lower and regexp_replace are there to prevent SQL injection. Yours, Laurenz Albe -- 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] NOT DEFERRABLE as default, why and how to manage it.
On Tue, 19 Aug 2008 10:49:11 +0200 Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: On Tue, 19 Aug 2008 11:20:08 +0300 Peter Eisentraut [EMAIL PROTECTED] wrote: Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: I just learnt that NOT DEFERRABLE is default. Is it mandated by SQL standard? Yes. Is there any reason they put it that way in the standard other than the mantra stricter is better? After reflecting a bit I think it is a matter of failing earlier. But it doesn't make things more transparent. Since there is no simple standard way to see which constraints are deferrable and no simple way to alter them. If you expect a constraint to be deferrable and it is not there are higher chances you'll have some warning. If you expect a constraint to be not deferrable but it is... the chances that something you're not expecting will silently happen are higher. But you can still get surprises in both cases. It would be nice to know some way which constraint are checked during a transaction so it would be easier to see wich ones you really need to defer and which one were declared as not deferrable. anyway are there guidelines on how/when changing directly the system tables? -- 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] CASE
Is there any control structure statement similar to select case ... ? If yes how to use it. I have tried to use, case when expression then expression end, but not worked well as I want to use other control structures and sql statements to be executed for each case. Please reply. Thanks CPK.
Re: [GENERAL] CASE
Hello Develop PL/pgSQL (PostgreSQL 8.4) has CASE statement. Actual and older version hasn't nothing similar. Regards Pavel Stehule 2008/8/19 c k [EMAIL PROTECTED]: Is there any control structure statement similar to select case ... ? If yes how to use it. I have tried to use, case when expression then expression end, but not worked well as I want to use other control structures and sql statements to be executed for each case. Please reply. Thanks CPK. -- 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] What's size of your PostgreSQL Database?
In response to Ow Mun Heng [EMAIL PROTECTED]: On Mon, 2008-08-18 at 11:01 -0400, justin wrote: Ow Mun Heng wrote: -Original Message- From: Scott Marlowe [EMAIL PROTECTED] If you're looking at read only / read mostly, then RAID5 or 6 might be a better choice than RAID-10. But RAID 10 is my default choice unless testing shows RAID-5/6 can beat it. I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives. Is this worst off than a RAID 5 implementation? I see no problem using Raid-0 on a purely read only database where there is a copy of the data somewhere else. RAID 0 gives performance. If one of the 3 drives dies it takes the server down and lost of data will happen. The idea behind RAID 1/5/6/10 is if a drive does fail the system can keep going.Giving you time to shut down and replace the bad disk or if you have hot swappable just pull and replace. I'm looking for purely read-only performance and since I didn't have the bandwidth to do extensive testing, I didn't know whether a RAID1 or a Raid 0 will do the better job. In the end, I decided to go with RAID 0 and now, I'm thinking if RAID1 will do a better job. When talking about pure read performance, the basic rule is the more spindles you can have active simultaneously, the better. By that rule, RAID 0 is the best, but you have to balance that with reliability. If you have 10 disks in a RAID 0, the chance of the entire system going down because of a disk failure is 10x that of a single disk system -- is that acceptable? In theory, you can have so many disks that the bottleneck moves to some other location, such as the IO bus or memory or the CPU, but I've never heard of that happening to anyone. Also, you want to get fast, high- quality disks, as 10 15,000 RPM disks are going to perform better than 10 7,200 RPM disks. Another solution is RAM, if you can get enough RAM in the system to hold your working set of data, then the speed of the disk is not really relevant. Of course, that's tough to do if you've got 3TB of data, which I don't know if that's your case or not. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
In response to Dale [EMAIL PROTECTED]: On Aug 18, 9:23 pm, Lew [EMAIL PROTECTED] wrote: Dale wrote: Hi, I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? http://www.postgresql.org/docs/8.3/static/sql-update.html does not list an INTO clause for UPDATE, and when you think about it, indeed such a clause doesn't make sense. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html As per this documentation, you should be able to do it. It works for the INSERT command, but not UPDATE. For the INSERT command, it makes my code look neater and I image it's more efficient too. Is it possible that your UPDATE command is updating multiple rows? I don't believe RETURNING will work on an UPDATE that touches more than 1 row. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] CASE
In response to c k [EMAIL PROTECTED]: Is there any control structure statement similar to select case ... ? If yes how to use it. I have tried to use, case when expression then expression end, but not worked well as I want to use other control structures and sql statements to be executed for each case. Personally, I don't understand the question. Perhaps if you provided an example of what you are trying to do with CASE WHEN and why it doesn't work for you. It _does_ sound like you need to be using something like pl/pgsql. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] CASE
Hi, I want to create a function as follows: case variable=value1 SQL statements case variable=value2 SQL statements case else end of case CPK On Tue, Aug 19, 2008 at 5:17 PM, Bill Moran [EMAIL PROTECTED]wrote: In response to c k [EMAIL PROTECTED]: Is there any control structure statement similar to select case ... ? If yes how to use it. I have tried to use, case when expression then expression end, but not worked well as I want to use other control structures and sql statements to be executed for each case. Personally, I don't understand the question. Perhaps if you provided an example of what you are trying to do with CASE WHEN and why it doesn't work for you. It _does_ sound like you need to be using something like pl/pgsql. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/http://people.collaborativefusion.com/%7Ewmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023
Re: [GENERAL] What's size of your PostgreSQL Database?
On Tue, 2008-08-19 at 02:28 -0400, David Wilson wrote: On Fri, Aug 15, 2008 at 11:42 PM, Amber [EMAIL PROTECTED] wrote: Dear all: We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list: I didn't realise the initial questions from this and since I'm lazy to look for the original mail, I'll put in my 2 cents worth. DB is a DSS type store instead of OLTP type. Heavily denormalised data. Master is a celeron 1.7Ghz, 768MB ram, 2x500GB 7200rpm IDE RAID1(data)+ 1 spare, 1x80GB (system). Slave is a celeron 1.7Ghz, 1.5GB RAM, 3x160GB 7200rpm IDE RAID1(data), 1x160GB system Max columns ~120 DB size is ~200+GB ~600+M (denormalised) rows in ~60+ tables (partitioned and otherwise) vacuum is done nightly in addition to turning on autovacuum. I'm both IO and CPU constrainted. :-) Denormalisation/ETL process is done on the master and only the final product is shipped to the slave for read-only via slony. I've got close to 8 indexes on each table (for bitmap scanning) Due to the denormalisation, gettin to the data is very snappy even based on such a small server. (adding ram to the slave saw drastic performance improvement over the initial 512MB) Currently looking for an FOSS implementation of a Slice and Dice kind of drilldown for reporting purposes. Tried a variety including pentaho, but never been able to get it set-up. -- 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] CASE
c k wrote: Hi, I want to create a function as follows: case variable=value1 SQL statements case variable=value2 SQL statements case else end of case Looks like you want to use pl/pgsl and IF ... THEN ... ELSE instead? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.
Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: Is there any reason they put it that way in the standard other than the mantra stricter is better? After reflecting a bit I think it is a matter of failing earlier. Deferrable constraints are an optional feature of SQL, and the reason this default is chosen is that systems with and without the feature behave the same. But it doesn't make things more transparent. Since there is no simple standard way to see which constraints are deferrable and no simple way to alter them. Query information_schema.table_constraints to find out about existing constraints and their parameters. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Idle in transcation problem?
i use the ps -ef | grep postgres to see all the connections. the connection's status is Idle in transcation. and i use the du -h /mydb to check the disk size. The disk size of mydb has increased from 400MB to 600MB. Why? because of the idle in transcation ? my pg version:8.1.3 OS: Linux Enterprise 4 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] default postgresql.conf
Hello List, When an initdb is done where do the default postgresql.conf and pg_hba.conf come from? Are they copied from some template file? Thanks, Steve -- 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] default postgresql.conf
Steve Clark a écrit : Hello List, When an initdb is done where do the default postgresql.conf and pg_hba.conf come from? Are they copied from some template file? They are copied from the share subdirectory. For example, in Debian, it's in: /usr/share/postgresql/major version number/postgresql.conf.sample And initdb customize them. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- 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.3 win32 crashing
Klint Gore [EMAIL PROTECTED] writes: Faulting application postgres.exe, version 8.3.3.8160, faulting module msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. I have a function that's doing a summary report counting data. It makes a couple of small temp tables then makes a big temp table. In the query that makes the big temp table, there's an immutable function that gets called thousands of times in the execution. In the immutable function, there was a raise notice for debugging . Without the raise, the query finishes in about 40sec. With it, the postgres.exe grinds down to about 2% cpu usage and eventually throws the message above. Then I reboot the machine - nothing responds to control. Please try to narrow it down a little. It seems like this could be caused by sending the messages to the postmaster log, or by sending them to the client, or by the client not processing them nicely. (You didn't say what client program you're using.) I'd suggest adjusting client_min_messages and log_min_messages so that the notice message goes to only one of the two places, and and then seeing what happens. regards, tom lane -- 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] How to execute 'set session role' from plpgsql function?
Albe Laurenz [EMAIL PROTECTED] writes: EXECUTE 'SET SESSION ROLE ' || lower(regexp_replace(wishedrole, '', '', 'g')) || ''; The lower and regexp_replace are there to prevent SQL injection. quote_ident() would be a far better solution. regards, tom lane -- 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] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
Bill Moran wrote: Is it possible that your UPDATE command is updating multiple rows? I don't believe RETURNING will work on an UPDATE that touches more than 1 row. Hmm, why not? alvherre= create table bill (a int, b text); CREATE TABLE alvherre= insert into bill values (1, 'one'); INSERT 0 1 alvherre= insert into bill values (2, 'two'); INSERT 0 1 alvherre= update bill set b = a || ' ' || b returning b; b --- 1 one 2 two (2 lignes) UPDATE 2 -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
In response to Alvaro Herrera [EMAIL PROTECTED]: Bill Moran wrote: Is it possible that your UPDATE command is updating multiple rows? I don't believe RETURNING will work on an UPDATE that touches more than 1 row. Hmm, why not? Because a doc linked to earlier in this thread said so. Can't find it now, so it's possible that I misread it or was suffering from temporal insanity. oops ... -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Idle in transcation problem?
Charles.Hou [EMAIL PROTECTED] writes: i use the ps -ef | grep postgres to see all the connections. the connection's status is Idle in transcation. and i use the du -h /mydb to check the disk size. The disk size of mydb has increased from 400MB to 600MB. Why? because of the idle in transcation ? Maybe. Old open transactions would prevent VACUUM from removing deleted row versions (because they might still be visible to those transactions). So if this condition persisted for a long time it could be responsible for table bloat. It's a good idea to make sure your client code doesn't leave transactions sitting open for long periods. my pg version:8.1.3 You really ought to get onto a more recent sub-release ... that one is 2.5 years old and has many known bugs. http://www.postgresql.org/docs/8.1/static/release.html regards, tom lane -- 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] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
Alvaro Herrera [EMAIL PROTECTED] writes: Bill Moran wrote: Is it possible that your UPDATE command is updating multiple rows? I don't believe RETURNING will work on an UPDATE that touches more than 1 row. Hmm, why not? plpgsql will reject UPDATE RETURNING INTO that returns more than one row, since it hasn't got any place to put the additional data. It wasn't clear to me whether the OP was even using plpgsql, but if he was, the example should have worked. We'd need to see more detail to guess what the real problem is. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updates and deletes with joins
I'm working on a CMS, one of the features of the CMS in question is that only one user can edit an item at any given time. I've implemented this by having one table that holds the items, and another table that holds locks. A lock row consists of the ID of the item locked, a timestamp indicating when the item was locked, an interval indicating when the locks expires and a string that holds a reason for the item currently being locked. I want to be able to restrict any query that updates or deletes from the articles table so that they can only occur if there isn't a corresponding entry in the locks table. As far as I can tell, however, you can't join tables when doing updates or deletes. I know on the PHP side I can attempt to do a select on the locks table and only perform the delete if the select returns 0 rows, but I'd rather the update or delete query itself does the checking. Can anyone help out? -- 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] PostgreSQL on Windows x64
Dave, Thanks for the link. Our app now running using EnterpriseDB 8.2 on x86. Plan to test them on x64 server once we got it. Do you know if jdbc, npgsql, PostgreSQL ANSI odbc drivers bundled in EnterpriseDB can run without any issues on x64? Thanks, Amin On Aug 19, 3:50 pm, [EMAIL PROTECTED] (Dave Page) wrote: On Mon, Aug 18, 2008 at 7:48 AM, Goboxe [EMAIL PROTECTED] wrote: Hi, Does PostgreSQL can run on Windows x64? If yes, which version? All versions (though you should start with 8.3.3):http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_... -- Dave Page EnterpriseDB UK:http://www.enterprisedb.com -- Sent via pgsql-general mailing list ([EMAIL PROTECTED]) 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] Updates and deletes with joins
On Tuesday 19 August 2008, Gordon [EMAIL PROTECTED] wrote: I want to be able to restrict any query that updates or deletes from the articles table so that they can only occur if there isn't a corresponding entry in the locks table. As far as I can tell, however, you can't join tables when doing updates or deletes. I know on the PHP side I can attempt to do a select on the locks table and only perform the delete if the select returns 0 rows, but I'd rather the update or delete query itself does the checking. Can anyone help out? The best way to implement this is through triggers on the target tables. However, you can also do joins with updates and deletes (UPDATE ... FROM and DELETE ... USING). -- Alan -- 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] What's size of your PostgreSQL Database?
On Tue, 2008-08-19 at 07:34 -0400, Bill Moran wrote: In theory, you can have so many disks that the bottleneck moves to some other location, such as the IO bus or memory or the CPU, but I've never heard of that happening to anyone. Also, you want to get fast, high- quality disks, as 10 15,000 RPM disks are going to perform better than 10 7,200 RPM disks. I've personally experienced this happening. -Mark -- 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] What's size of your PostgreSQL Database?
On Sat, 2008-08-16 at 11:42 +0800, Amber wrote: Dear all: We are currently considering using PostgreSQL to host a read only warehouse, we would like to get some experiences, best practices and performance metrics from the user community, following is the question list: 1. What's size of your database? 2. What Operating System are you using? 3. What level is your RAID array? 4. How many cores and memory does your server have? 5. What about your performance of join operations? 6. What about your performance of load operations? 7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning. 8. Single instance or a cluster, what cluster software are you using if you have a cluster? Thank you in advance! 1. 2.5-3TB, several others that are of fractional sisize. ... 5. They do pretty well, actually. Our aggregate fact tables regularly join to metadata tables and we have an average query return time of 10-30s. We do make some usage of denormalized mviews for chained/hierarchical metadata tables. 6. Load/copy operations are extremely performant. We pretty well constantly have 10+ concurrent load operations going with 2-3 aggregation processes. 7. About 50, but I'm not sure what the transfer rate is. 8. We have a master and a replica. We have plans to move to a cluster/grid Soon(TM). It's not an emergency and Postgres can easily handle and scale to a 3TB database on reasonable hardware ($30k). A few notes: our database really can be broken into a very typical ETL database: medium/high input (write) volume with low latency access required. I can provide a developer's view of what is necessary to keep a database of this size running, but I'm under no illusion that it's actually a large database. I'd go into more details, but I'd hate to be rambling. If anyone's actually interested about any specific parts, feel free to ask. :) Also, if you feel that we're doing something wrong, feel free to comment there too. :) -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cache lookup failed
I got following error while testing some newly created functions. ERROR: cache lookup failed for function 111462 CONTEXT: PL/pgSQL function uf_postdoc line 25 at FOR over SELECT rows ** Error ** ERROR: cache lookup failed for function 111462 SQL state: XX000 Context: PL/pgSQL function uf_postdoc line 25 at FOR over SELECT rows what this means? and how to solve it? I searched manual but not got much help. Thanks and Regards, CPK
[GENERAL] DELETE
hello, what will be the error in syntax of following statement? delete from accountingtransactions where accountingtransactions.refaccdocid=docs.docid and docs.tmpselect=-1 and docs.tmpselectedby= $1; when a new function is created it does not gives any error but when this function is called from outside it gives syntax error as 'missing FROM-clause entry for table docs'. Why? any clue? Regards, CPK
Re: [GENERAL] DELETE
Add the USING list clause: usinglist A list of table expressions, allowing columns from other tables to appear in the WHERE condition. This is similar to the list of tables that can be specified in the FROM Clause http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-FROM of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table in the usinglist, unless you wish to set up a self-join. See this page for more info: http://www.postgresql.org/docs/8.3/static/sql-delete.html So something like this: delete from . using DOCS where .. Full syntax description: DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ AS output_name ] [, ...] ] From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of c k Sent: Tuesday, August 19, 2008 3:04 PM To: pgsql-general@postgresql.org Subject: [GENERAL] DELETE hello, what will be the error in syntax of following statement? delete from accountingtransactions where accountingtransactions.refaccdocid=docs.docid and docs.tmpselect=-1 and docs.tmpselectedby= $1; when a new function is created it does not gives any error but when this function is called from outside it gives syntax error as 'missing FROM-clause entry for table docs'. Why? any clue? Regards, CPK
Re: [GENERAL] Pg dump Error
tuanhoanganh escribió: I have a problem with pg_dump on 2 computers. On the first computer, pg_dump runs very well, but on the second pg_dump has a error : pg_dump: reading triggers for table ph57 pg_dump: reading triggers for table phts pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error message from server: message contents do not agree with length in message type D message contents do not agree with length in message type c server sent data (D message) without prior row description (T message) pg_dump: The command was: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' ORDER BY 1,2 pg_dump: *** aborted because of error What Postgres version is the server, what's the pg_dump version on each of these computers, and how are you invoking pg_dump? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] PostgreSQL on Windows x64
On 8/19/08, Goboxe [EMAIL PROTECTED] wrote: Dave, Thanks for the link. np. Our app now running using EnterpriseDB 8.2 on x86. Plan to test them on x64 server once we got it. Do you know if jdbc, npgsql, PostgreSQL ANSI odbc drivers bundled in EnterpriseDB can run without any issues on x64? The jdbc drivers and npgsql should be fine. The release version of the odbc drivers are 32 bit and thus will only work with 32 bit applications (though, you can run them on a 64 bit OS). There is an experimental 64bit version of the driver here: http://www.geocities.jp/inocchichichi/psqlodbc/ But... are all your clients 64 bit as well? If it's just the server, then just run the 32bit drivers on the 32bit clients - the server will talk to either. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL archiving to network drive
I'm setting up WAL archiving on a Windows machine need to copy the WAL files to a network drive. Is it best to give the 'postgres' user network access archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled task to move them to the network drive? (Or something else entirely?) Thanks, --Rob Adams -- 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] WAL archiving to network drive
Rob Adams wrote: I'm setting up WAL archiving on a Windows machine need to copy the WAL files to a network drive. Is it best to give the 'postgres' user network access archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled task to move them to the network drive? (Or something else entirely?) I am archiving them directly, from a Linux installation, to a Windows machine. As long as the error handling is sound, it should work OK. I've had no problems other than some the expected down time causing WAL files to pile up. It's been working great for a couple years now. I write the files with an alternate file name, then rename them. That helps make sure half written files are not mistaken for fully written ones. I also have a little watch dog script that runs on cron every few minutes, that counts the number of WAL files present in the xlog directory. If that count is oddly high, I get an email. I've been alerted to, and fixed quickly, a problem three or four times this way. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] schema name in SQL statement.
I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table -- 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] schema name in SQL statement.
-- Original message -- From: Masis, Alexander (US SSA) [EMAIL PROTECTED] I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table -- See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html -- Adrian Klaver [EMAIL PROTECTED] -- 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 get UPDATE ... RETURNING ... INTO ... to compile successfully
Hi All, In the following documentation it advises that the UPDATE statement should be able to return a value into a variable in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html It works for the INSERT command, but not UPDATE. For the INSERT command, it makes my code look neater and I image it's more efficient too. This time I am trying to UPDATE a field using a primary key, and return another field into a variable so that I can take necessary action if required later in the plpgsql script. I know that I can issue another SELECT query to retrieve the information, but I would have thought it would be a lot more efficient to return the value during the UPDATE. Regards, Dale. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, 20 August 2008 1:30 To: Alvaro Herrera Cc: Bill Moran; Dale; pgsql-general@postgresql.org Subject: Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully Alvaro Herrera [EMAIL PROTECTED] writes: Bill Moran wrote: Is it possible that your UPDATE command is updating multiple rows? I don't believe RETURNING will work on an UPDATE that touches more than 1 row. Hmm, why not? plpgsql will reject UPDATE RETURNING INTO that returns more than one row, since it hasn't got any place to put the additional data. It wasn't clear to me whether the OP was even using plpgsql, but if he was, the example should have worked. We'd need to see more detail to guess what the real problem is. regards, tom lane -- 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] 8.3.3 win32 crashing
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Faulting application postgres.exe, version 8.3.3.8160, faulting module msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. I have a function that's doing a summary report counting data. It makes a couple of small temp tables then makes a big temp table. In the query that makes the big temp table, there's an immutable function that gets called thousands of times in the execution. In the immutable function, there was a raise notice for debugging . Without the raise, the query finishes in about 40sec. With it, the postgres.exe grinds down to about 2% cpu usage and eventually throws the message above. Then I reboot the machine - nothing responds to control. Please try to narrow it down a little. It seems like this could be caused by sending the messages to the postmaster log, or by sending them to the client, or by the client not processing them nicely. (You didn't say what client program you're using.) I'd suggest adjusting client_min_messages and log_min_messages so that the notice message goes to only one of the two places, and and then seeing what happens. Client is pgAdmin from the 8.3.3 installer. client=warning, log=warning completes client=warning, log=notice fails client=notice, log=warning completes client=notice, log=notice fails It fails a heck of a lot quicker with client=warning, log=notice. This is the raise that causes it CREATE OR REPLACE FUNCTION stagecode(date, date) RETURNS text AS $BODY$ declare TimeSpan integer = $2 - $1; begin raise notice '%',TimeSpan; return case when $1 is null or $2 is null then 'X' when TimeSpan 10 then 'B' when TimeSpan 70 then 'L' when TimeSpan 120 then 'W' when TimeSpan 330 then 'P' when Timespan 450 then 'Y' when Timespan 700 then 'H' else 'A' end; end;$BODY$ klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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.3 win32 crashing
Klint Gore [EMAIL PROTECTED] writes: [ heavy RAISE NOTICE traffic crashes the server ] Tom Lane wrote: Please try to narrow it down a little. client=warning, log=warning completes client=warning, log=notice fails client=notice, log=warning completes client=notice, log=notice fails It fails a heck of a lot quicker with client=warning, log=notice. Okay, so the problem is definitely on the postmaster-log side. Please show us all of your logging-related configuration settings. ISTR that Magnus was poking at some problem in the WIN32 log collector code, but whether this is related is not yet clear ... regards, tom lane -- 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 get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale Harris [EMAIL PROTECTED] writes: In the following documentation it advises that the UPDATE statement should be able to return a value into a variable in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html It works for the INSERT command, but not UPDATE. As was already suggested, if you want help on this you are going to need to exhibit a complete problem case. Blanket assertions that are demonstrably false don't advance the cause for anyone. regards, tom lane -- 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.3 win32 crashing
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: [ heavy RAISE NOTICE traffic crashes the server ] client=warning, log=warning completes client=warning, log=notice fails client=notice, log=warning completes client=notice, log=notice fails It fails a heck of a lot quicker with client=warning, log=notice. Okay, so the problem is definitely on the postmaster-log side. Please show us all of your logging-related configuration settings. I can duplicate it on my notebook with a slightly smaller set of data (pentium M, 2ghz, 1g mem, 5400rpm pata drive 23gig free). Both server and notebook were installed off the same installer. All settings not commented. The log_min_messages is only there from the test runs. It's normally the default. port = 5432# (change requires restart) max_connections = 100# (change requires restart) shared_buffers = 32MB# min 128kB or max_connections*16kB max_fsm_pages = 204800# min max_fsm_relations*16, 6 bytes each log_destination = 'stderr'# Valid values are combinations of logging_collector = on# Enable capturing of stderr and csvlog log_min_messages = warning log_line_prefix = '%t '# special values: datestyle = 'iso, mdy' lc_messages = 'C'# locale for system error message lc_monetary = 'C'# locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C'# locale for time formatting default_text_search_config = 'pg_catalog.english' The notebook as visual studio 2005 professional if that helps but it's not setup to build postgres. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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 get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale Harris wrote:
It works for the INSERT command, but not UPDATE. For the INSERT command, it
makes my code look neater and I image it's more efficient too.
This time I am trying to UPDATE a field using a primary key, and return
another field into a variable so that I can take necessary action if
required later in the plpgsql script. I know that I can issue another
SELECT query to retrieve the information, but I would have thought it would
be a lot more efficient to return the value during the UPDATE.
Works for me
test=# begin;
BEGIN
test=#
test=# create table foo(f1 int, f2 text);
CREATE TABLE
test=# insert into foo values(1, 'hi');
INSERT 0 1
test=# insert into foo values(2, 'hello');
INSERT 0 1
test=#
test=# create function bar(int,int) returns boolean as $$
test$# declare
test$#r record;
test$# begin
test$#update foo set f1 = $2 where f1 = $1 returning * into r;
test$#raise notice '% %',r.f1,r.f2;
test$#return true;
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# create function bar1(text) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# for r in
test$#update foo set f2 = f2 || $1 returning *
test$# loop
test$#raise notice '% %',r.f1,r.f2;
test$# end loop;
test$# return true;
test$# end;
test$# $$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select * from bar(2,3);
NOTICE: 3 hello
bar
-
t
(1 row)
test=#
test=# select * from bar1('!');
NOTICE: 1 hi!
NOTICE: 3 hello!
bar1
--
t
(1 row)
test=#
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
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 get UPDATE ... RETURNING ... INTO ... to compile successfully
As per the original message:
I've got some code which postgres 8.3.3 won't accept. Postgres doesn't
like the INTO clause on RETURNING INTO and I've tried following the
documentation.
UPDATE EntityRelation SET Status = inStatus, Modified =
Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID =
inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID,
RelatedID) RETURNING Default INTO oldDefault;
Does anyone have any ideas if the INTO clause actually works at all for an
UPDATE statement?
And documentation link which advises that the UPDATE statement should be
able to return a value into a variable in plpgsql.
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
The query above is out of my plpgsql script and the WHERE clause selects an
unique record. Therefore only 1 value should ever be returned. The point
is that I don't even get that far as the script fails to compile due to the
INTO clause.
Regards,
Dale.
-Original Message-
From: Klint Gore [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 20 August 2008 12:47
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to
compile successfully
Dale Harris wrote:
It works for the INSERT command, but not UPDATE. For the INSERT command,
it
makes my code look neater and I image it's more efficient too.
This time I am trying to UPDATE a field using a primary key, and return
another field into a variable so that I can take necessary action if
required later in the plpgsql script. I know that I can issue another
SELECT query to retrieve the information, but I would have thought it
would
be a lot more efficient to return the value during the UPDATE.
Works for me
test=# begin;
BEGIN
test=#
test=# create table foo(f1 int, f2 text);
CREATE TABLE
test=# insert into foo values(1, 'hi');
INSERT 0 1
test=# insert into foo values(2, 'hello');
INSERT 0 1
test=#
test=# create function bar(int,int) returns boolean as $$
test$# declare
test$#r record;
test$# begin
test$#update foo set f1 = $2 where f1 = $1 returning * into r;
test$#raise notice '% %',r.f1,r.f2;
test$#return true;
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# create function bar1(text) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# for r in
test$#update foo set f2 = f2 || $1 returning *
test$# loop
test$#raise notice '% %',r.f1,r.f2;
test$# end loop;
test$# return true;
test$# end;
test$# $$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select * from bar(2,3);
NOTICE: 3 hello
bar
-
t
(1 row)
test=#
test=# select * from bar1('!');
NOTICE: 1 hi!
NOTICE: 3 hello!
bar1
--
t
(1 row)
test=#
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
Re: [GENERAL] schema name in SQL statement.
On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: -- Original message -- From: Masis, Alexander (US SSA) [EMAIL PROTECTED] I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table -- See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html -- Adrian Klaver [EMAIL PROTECTED] I see answers like this all the time. When I review the doc's I still don't know how to set the search_path because there is no example in the doc's. Do I do something like this: select search_path=(public) or select search_path=public . So how is the search_path set? -- John Fabiani -- 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 get UPDATE ... RETURNING ... INTO ... to compile successfully
On Tue, Aug 19, 2008 at 9:51 PM, Dale Harris [EMAIL PROTECTED] wrote: As per the original message: UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; This is called a code fragment. What people want to see here is a self-contained example of it failing. Until you post one of those, no one can troubleshoot it because it WORKS FOR THEM. Create a test table insert some data create a plpgsql function call that function and have it throw an error. Post all of that here. -- 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] schema name in SQL statement.
On Tue, Aug 19, 2008 at 10:53 PM, johnf [EMAIL PROTECTED] wrote: On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: From: Masis, Alexander (US SSA) [EMAIL PROTECTED] I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html I see answers like this all the time. When I review the doc's I still don't know how to set the search_path because there is no example in the doc's. Do I do something like this: select search_path=(public) or select search_path=public . So how is the search_path set? But there is a link on that page under search_path that points here: http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html which has much better info on search_path in 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] can't get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale Harris wrote: As per the original message: I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. UPDATE EntityRelation SET Status = inStatus, Modified = Session_TimeStamp(), ModifiedBy = UserID() WHERE (RelationID = inRelationID) AND (EntityID = inEnityID) AND IsEqual(inRelatedID, RelatedID) RETURNING Default INTO oldDefault; Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement? And documentation link which advises that the UPDATE statement should be able to *return a value into a variable* in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html The query above is out of my plpgsql script and the WHERE clause selects an unique record. Therefore only 1 value should ever be returned. The point is that I don’t even get that far as the script fails to compile due to the INTO clause. Show us the whole function - then we can try it and see where the problem is. What is the actual message you get? Are you sure you spelled entity right in inEnityID (need another T perhaps)? Does select version() actually say 8.3.3? The statement you posted works for me. test=# begin; BEGIN test=# test=# create table EntityRelation test-# (EntityID int, test(# Status int, test(# Modified timestamp, test(# ModifiedBy text, test(# RelationID int, test(# RelatedID int, test(# Default text); CREATE TABLE test=# test=# insert into EntityRelation test-# values (1,1,now(), 'me', 1,1,'hello'); INSERT 0 1 test=# test=# create or replace function foo() returns boolean as $$ test$# declare test$# oldDefault text; test$# instatus int = 1; test$# inRelationID int = 1; test$# inRelatedID int = 1; test$# inEnityID int = 1; test$# test$# begin test$# test$# UPDATE EntityRelation test$# SET Status = inStatus, test$# Modified = now(), test$# ModifiedBy =current_user test$# WHERE (RelationID = inRelationID) test$# AND (EntityID = inEnityID) test$# AND inRelatedID = RelatedID test$# RETURNING Default test$# INTO oldDefault; test$# test$# raise notice '%', oldDefault; test$# return false; test$# test$# end;$$ language plpgsql volatile; CREATE FUNCTION test=# test=# select foo(); NOTICE: hello foo - f (1 row) test=# klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] schema name in SQL statement.
On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote: On Tue, Aug 19, 2008 at 10:53 PM, johnf [EMAIL PROTECTED] wrote: On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: From: Masis, Alexander (US SSA) [EMAIL PROTECTED] I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.htm l I see answers like this all the time. When I review the doc's I still don't know how to set the search_path because there is no example in the doc's. Do I do something like this: select search_path=(public) or select search_path=public . So how is the search_path set? But there is a link on that page under search_path that points here: http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html which has much better info on search_path in it. Thanks - I did not note the link. -- John Fabiani -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: How do I determine my data dir for a created database for pg_ctl?
Hi, I have a database I can psql into... How can I determine what its absolute path is so I can use pg_ctl on it to restart it? Because when I use pg_ctl, it tells me I have to provide it in the -D flag as an argument. Thanks, matt -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: Restarting with pg_ctl, users, and passwords.
Hi, I just installed postgresql 8.3 on Ubuntu Heron with Postgis. I've worked with this install on XP before, but not on Linux. I'm having trouble telling if the postmaster is started. But, maybe more root to the problem, I cannot log onto the database via 'psql' when I supply what I think I set as the correct password. I've created a database and can log into it and do stuff with the tables using psql. However, I have a CGI app that wants to call the database (all on the same machine), but gets a 'FATAL: Ident authentication failed for user postgres' error. now, while logged onto my Linux user account 'postgres', I can psql into my database without having to provide a password. So, while in there, I issued the following SQL: ALTER USER postgres WITH PASSWORD 'postgres'; now, I *thought* what that would do would be to allow me to issue the 'psql' command from my regular non-postgres Linux account and log in as long as I would issue: psql -d mydb -U postgres -W and then provide 'postgres' as the password as well, as I had changed it in mydb as previously stated. But I am denied access when I try this from my account. This is what is confusing to me. I suspect that this may be at the core of why I cannot connect to mydb, but I am not sure, as I cannot even confirm that the database is running as I thought it would, since I don't know what process to look for in the 'ps -ef' dump. Please advise, thanks! Matt -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- 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] schema name in SQL statement.
-Original Message- From: johnf [EMAIL PROTECTED] To: pgsql-general@postgresql.org Subject: Re: [GENERAL] schema name in SQL statement. Date: Tue, 19 Aug 2008 22:25:14 -0700 On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote: On Tue, Aug 19, 2008 at 10:53 PM, johnf [EMAIL PROTECTED] wrote: On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: From: Masis, Alexander (US SSA) [EMAIL PROTECTED] I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.htm l I see answers like this all the time. When I review the doc's I still don't know how to set the search_path because there is no example in the doc's. Do I do something like this: select search_path=(public) or select search_path=public . So how is the search_path set? set search_path = 'xmxmxmxmxmxm' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
