[GENERAL] DTrace and PostgreSQL
I've got Solaris 10 11/06 on my PC. I removed the static keyword in src/backend/access/transam/xact.c. for the AbortTransaction and CommitTransaction functions declarations and compiled 8.2.3. Everything works nicely. I was wondering if DTrace could tell me how many inserts are being done in a pl/pgsql function while in a loop for example. As you know a pl/pgsql function executes in a single transaction so the DTrace probe transaction__commit(int) I believe is not helpful here. Could DTrace measure how many inserts are being done in a transaction that has not yet been commited, especially if that transaction block is in a pl/pgsql function? This would be extremely useful as when one has a bunch of inserts one could be able to see how far along the pl/pgsql function was. regards, karen ---(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
[GENERAL] How do I use returning in a view?
CREATE RULE ins_productionlog AS ON INSERT TO vwProductionlog DO INSTEAD ( INSERT INTO PRODUCTIONLOG (machine_name,product_serial_id,production_time,product_number,id) VALUES (new.machine_name, new.product_serial_id, new.production_time,new.product_number, DEFAULT) RETURNING productionlog.machine_name, productionlog.product_serial_id, productionlog.production_time, productionlog.product_number, productionlog.id AS foreign_id; INSERT INTO TTEST (name, id) VALUES (new.name, vwProductionlog.foreign_id ) ; ); I have an updateable view (using rules) that I'm trying to improve by using 8.2's RETURNING feature to place the result of one insert into the next. I want to be able to put the returning productionlog.id AS foreign_id into table TTEST. Is that even possible just using RULES? If it is, what would be the correct syntax? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Npgsql and 57014 query_canceled error message
I'm doing some testing on a larger dataset, and I've started getting a 57014 error message when I catch an NpgsqlException. I thought it might be timing out on me, so in the connection string I've set the time out settings to the maximum of 1024 seconds before timeout. Has anyone else experienced this, and is there a known solution? Thank you. regards, Karen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 9.0
On Jan 29, 11:06 pm, [EMAIL PROTECTED] (Dawid Kuroczko) wrote: * updatable views [ or am I missing something? ] -- it seems to me they were close to be completed, but I don't remember if they were completed and committed or not. PostgreSQL has updatable views via the rules system. I use updatable views all the time in postgres. ---(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
[GENERAL] Can a function be parameter in PL/PGSQL function?
Is it possible to have a pl/pgsql function take another pl/pgsql function as one of the parameters? regards, karen ---(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
[GENERAL] PostgreSQL 9.0
I was just looking at all the upcoming features scheduled to make it into 8.3, and with all those goodies, wouldn't it make sense for this to be a 9.0 release instead of an 8.3? It looks like postgresql is rapidly catching up to oracle if 8.3 branch gets every feature scheduled for it. About the only big features pg 8.3 doesn't have is materialized views and RMAN.. Now that PostgreSQL is getting so close to oracle functionality, is there any worry in the community that oracle will begin to target postgres like they're targeting mySQL? regards, karen ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PostgreSQL 9.0
I was just looking at all the upcoming features scheduled to make it into 8.3, and with all those goodies, wouldn't it make sense for this to be a 9.0 release instead of an 8.3? It looks like postgresql is rapidly catching up to oracle if 8.3 branch gets every feature scheduled for it. About the only big features pg 8.3 doesn't have is materialized views and RMAN.. Now that PostgreSQL is getting so close to oracle functionality, is there any worry in the community that oracle will begin to target postgres like they're targeting mySQL? regards, karen ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Can you specify the pg_xlog location from a config file?
Windows doesn't support symlinks. Is it possible instead for there to be a config file that lets one set where the pg_xlog directory will sit? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Rollback using WAL files?
On Jan 26, 9:45 am, [EMAIL PROTECTED] (Tom Lane) wrote: Florian Weimer [EMAIL PROTECTED] writes: In theory, this should be possible (especially if you haven't switched off full page writes).Not really --- the WAL records are not designed to carry full information about the preceding state of the page, so you can't use them to undo. (Example: a DELETE record says which tuple was deleted, but not what was in it.) It would be really useful if one had the option of allowing the WAL records to keep track of what was in a tuple as evidenced here. I use triggers on every production table to record every change to log tables (which have rules to prevent deleting and updating). Allowing the option of having the WAL do this seems like a good idea... regards, karen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Spam from EnterpriseDB?
Alan Hodgson wrote: On Thursday 18 January 2007 15:54, Steve Atkins [EMAIL PROTECTED] wrote: Anyone else get spam from EnterpriseDB today, talking about Postgresql Support Services? yep. You really would think that even the marketing weenies might know better by now. I do think that the unsolicited email was not an effective marketing technique at all. Yet, I do not consider it on the same level as SPAM (even though it may fit that description) because I understand that many of the developers at that company are actively contributing to PostgreSQL. The reality is that postgres _is_ open source which makes it difficult to create a viable business model because most people will just download postgresql for free and get support on the mailing lists. What I think it suggests is that the company is having some financial difficulties. The sad thing is that in order for PostgreSQL to remain competitive, it probably needs paid developers working on it. Oracle and the rest have teams of professionals working on their RDBMS software 8 or more hours per day. It is naive to think that someone coming home after work spending 2 hours a day can compete with a professional team working full time. I think a good business market would be PostgreSQL hosting. Solaris 10 with PostgreSQL on a Zone. Maybe another server with OpenBSD. They could then upsell their support to those hosted sites that have growing traffic. As for mass emailing, I think instead they should create a newsletter they send out to subscribers. It could be filled with useful tips and ideas (at the bottom they could provide their contact information and services provided). This way, people would look forward to reading their material and have positive associations of the company. Just my 2 cents Regards, Karen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?
Chris Mair wrote: I have OS X tiger with all the updates: uname -r 8.8.0 Here is what I get when I try to initdb on a freshly compiled 8.2: selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=2, size=1646592, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1646592 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1 Works for me :| (see initdb output below)... I read the documentation (http://www.postgresql.org/docs/8.2/static/kernel-resources.html) and added the appropriate items to /etc/sysctl.conf, and I rebooted for it to take effect. cat /etc/sysctl.conf kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 Can you check whether the settings worked? Do: ibook:~ chris$ sysctl -a | grep shm kern.sysv.shmmax: 4194304 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 Bye, Chris. Here is what I get: sysctl -a | grep shm kern.sysv.shmmax: 4194304 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 This is very strange as I just updated OS X with the latest updates and then compiled and installed 8.2. I have used OS X Tiger and postgresql 8.1 compiled from source with no problem. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?
Tom Lane wrote: Karen Hill [EMAIL PROTECTED] writes: I still get the error when I initdb. OS X and PostgreSQL has worked before for me, compiled from the source. Works for me. What do you get from sysctl -a | grep sysv ? sysctl -a | grep sysv kern.sysv.shmmax: 4194304 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 kern.sysv.semmni: 87381 kern.sysv.semmns: 87381 kern.sysv.semmnu: 87381 kern.sysv.semmsl: 87381 kern.sysv.semume: 10 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] OS X Tiger, and PostgreSQL 8.2 don't mix?
I have OS X tiger with all the updates: uname -r 8.8.0 Here is what I get when I try to initdb on a freshly compiled 8.2: selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=2, size=1646592, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1646592 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1 I read the documentation (http://www.postgresql.org/docs/8.2/static/kernel-resources.html) and added the appropriate items to /etc/sysctl.conf, and I rebooted for it to take effect. cat /etc/sysctl.conf kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 I still get the error when I initdb. OS X and PostgreSQL has worked before for me, compiled from the source. regards, karen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL, LGPL and GPL.
I was looking through the various contrib packages and pgfoundry projects. I noticed that many of them are GPL like PostGIS or LGPL like Npgsql. I have questions. If you make create a PostgreSQL database that uses PostGIS and you distribute that database, than your database (tables, stored procedures, views, etc) are GPL? Like wise if you create a client that connects to that database, do they also become GPL? Does PostgreSQL in effect become GPL when using PostGIS because PostGIS accesses parts of PostgreSQL? Npgsql is LGPL. It means you must release the source of Npgsql when distributing it, and if you modify Npgsql, but not have to release the source under the (L)GPL of the software that calls Npgsql functions? If you provide the source on a CD and the (GPL/LGPL) license as a text file on that CD if you distribute, then are your obligations met under the GPL/LGPL? What if those you distribute to lose the source code CD, can they then come after you X number of years later demanding the source? For the developers of LGPL/GPL like Npgsql, why do you not dual license? Have a model like MySQL where one can purchase a BSD licensed version or use the GPL/LGPL one. regards, Karen ---(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] division by zero error in a request
Bernard Grosperrin wrote: I wants to make a view giving me some statistics. I am not sure to understand why something like this SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) / (sold_parts_amount_dly + sold_labor_amount_dly) from sales give me a division by zero error? If that is not the way to go, should I write a function that I would call instead? Thanks, Bernard Hi Bernard, In mathematics, you cannot divide by zero. So 4/0 is not possible for example. In your SELECT query, sold_parts_amount_dly and sold_labor_amunt_dly are zero in some cases, giving you the division by zero error. You could solve this by using CASE. http://www.postgresql.org/docs/8.1/static/functions-conditional.html Or you could create a pl/pgsql function that 1.) either uses exceptions to handle the division by zero error or 2.) check that sold_parts_amount_dly and sold_labor_amount_dly are not zero before dividing by them within a function. The simplest would be to use CASE in your query. regards, karen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] more anti-postgresql FUD
Merlin Moncure wrote: SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N; using offset to walk a table is extremely poor form because of: * poor performance * single user mentality * flat file mentality databases are lousy at this becuase they inheritly do not support abolute addressing of data -- nore should they, beause this is not what sql is all about. in short, 'offset' is a hack, albeit a useful one in some cases, but dont gripe when it doesn't deliver the goods. for server side browsing use cursors or a hybrid pl/pgqsl loop. for client side, browse fetching relative to the last key: select * from foo where p p1 order by p limit k; in 8.2, we get proper comparisons so you can do this with multiple part keys: select * from foo where (a1,b1,b1) (a,b,c) order by a,b,c limit k; I have 8.2 Beta 1 (Win32) on my home pc and offset was faster than fetching relative to the last key as measured by explain analyze. This was on a table with about 1,000 rows. regards, karen ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Find out the number of rows returned by refcursor?
Karen Hill wrote: Tom Lane wrote: Karen Hill [EMAIL PROTECTED] writes: -- Is there a way to know the total number of rows the cursor is capable of traversing without using --count? If you want an accurate count, the only way is to traverse the cursor. Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE BACKWARD ALL to reset the cursor (the latter at least should be reasonably cheap). Cool. Quick question, how does one go about noting the rowcount? Using the rowcount in get diagnostics or something else? A MOVE FORWARD ALL FROM cur; statement returns MOVE x. Where x is the number moved. The result seems to be of a NOTICE type, and I'm not sure how I can pass that as a result from a pgsql function. I guess what I'm looking for is this, if it is possible: CREATE OR REPLACE FUNCTION FOOBAR(refcursor , out refcursor , out total int4) AS ' BEGIN OPEN $1 FOR SELECT * FROM t_table ORDER by c_column DESC; total := (MOVE FORWARD ALL FROM $1); MOVE BACKWARD ALL FROM $1; $2 := $1; END; ' LANGUAGE plpgsql; Thanks in advance. Also, is this possible? I would like to be able to plug in the name of the refcursor returned by the above stored procedure and be able to fetch data: CREATE OR REPLACE FUNCTION MOVE(refcursor) RETURNS ROWTYPE AS ' BEGIN FETCH FORWARD 20 FROM $1; END; ' LANGUAGE plpgsql; regards, karen. ---(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] Find out the number of rows returned by refcursor?
Karen Hill wrote:
What is the best way to find out the total number of rows returned by
an refcursor? This would allow the client user to know the total
amount of rows as they are using FETCH FORWARD/BACKWARD.
For example let's say that an refcursor has 300 rows. The user fetches
20 at a time. I would like the user to know that there are 300
possible rows.
I probably should re-phrase that question.
CREATE OR REPLACE FUNCTION foobar( refcursor ) RETURNS refcurser AS '
BEGIN
OPEN $1 FOR SELECT * FROM t ORDER by z;
END;
' LANGUAGE 'plpgsql';
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Is there a way to know the total number of rows the cursor is
capable of traversing without using --count? Perhaps GET DIAGNOSTICS
ROW_COUNT?
SELECT foobar('mycursor');
-- I want to avoid using count(*) for performance reasons. Getting the
total number of rows the cursor --has. I suspect it there is a system
variable that has this information...I just don't know which one it
--is.
SELECT COUNT(*) FROM t;
COMMIT;
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Find out the number of rows returned by refcursor?
Tom Lane wrote: Karen Hill [EMAIL PROTECTED] writes: -- Is there a way to know the total number of rows the cursor is capable of traversing without using --count? If you want an accurate count, the only way is to traverse the cursor. Consider using MOVE FORWARD ALL and noting the rowcount, then MOVE BACKWARD ALL to reset the cursor (the latter at least should be reasonably cheap). Cool. Quick question, how does one go about noting the rowcount? Using the rowcount in get diagnostics or something else? regards, karen. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Find out the number of rows returned by refcursor?
What is the best way to find out the total number of rows returned by an refcursor? This would allow the client user to know the total amount of rows as they are using FETCH FORWARD/BACKWARD. For example let's say that an refcursor has 300 rows. The user fetches 20 at a time. I would like the user to know that there are 300 possible rows. regards, karen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] refcursor error 55000
I get an error message 55000 when I try to traverse backwards in an refcursor. Works fine going forward. The hint says I need to use scroll. What is the syntax for using scroll in a stored procedure that returns an refcursor? Or do refcursors only support traversing forward? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] cyclical redundancy checksum algorithm(s)?
I just finished reading one of Ralph Kimball's books. In it he mentions something called a cyclical redundancy checksum (crc) function. A crc function is a hash function that generates a checksum. I am wondering a few things. A crc function would be extremely useful and time saving in determining if a row needs to be updated or not (are the values the same, if yes don't update, if not update). In fact Ralph Kimball states that this is a way to check for changes. You just have an extra column for the crc checksum. When you go to update data, generate a crc checksum and compare it to the one in the crc column. If they are same, your data has not changed. Yet what happens if there is a collision of the checksum for a row? Ralph Kimball did not mention which algorithm to use, nor how to create a crc function that would not have collisions. He does have a PhD, and a leader in the OLAP datawarehouse world, so I assume there is a good solution. Is there a crc function in postgresql? If not what algorithm would I need to use to create one in pl/pgsql? regards, karen ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
Gene Wirchenko wrote: I just finished reading one of Ralph Kimball's books. In it he mentions something called a cyclical redundancy checksum (crc) function. A crc function is a hash function that generates a checksum. I am wondering a few things. A crc function would be extremely useful and time saving in determining if a row needs to be updated or not (are the values the same, if yes don't update, if not update). In fact Ralph Kimball states that this is a way to check for changes. You just have an extra column for the crc checksum. When you go to update data, generate a crc checksum and compare it to the one in the crc column. If they are same, your data has not changed. Yet what happens if there is a collision of the checksum for a row? Then you get told that no change has occurred when one has. I would call this an error. That's exactly what I thought when I read that in his book. I was thinking back to the sha1 and md5 algorithms, maybe a special crc algorithm is safe from this. Ralph Kimball did not mention which algorithm to use, nor how to create a crc function that would not have collisions. He does have a PhD, and a leader in the OLAP datawarehouse world, so I assume there is a good solution. Your error. Having a Ph.D. does not stop someone from being wrong. Is there a crc function in postgresql? If not what algorithm would I need to use to create one in pl/pgsql? I think you are focusing on irrelevant minutiae. Is the performance really that bad that you have go to odd lengths to up it? It is not for performance. It is to save time writing a lot of stored procedure code. when you hav e an updateable view with 70 values that need to be checked for changes a checksum starts to sound very appealing. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
Tom Lane wrote: Karen Hill [EMAIL PROTECTED] writes: Ralph Kimball states that this is a way to check for changes. You just have an extra column for the crc checksum. When you go to update data, generate a crc checksum and compare it to the one in the crc column. If they are same, your data has not changed. You sure that's actually what he said? A change in CRC proves the data changed, but lack of a change does not prove it didn't. On page 100 in the book, The Data Warehouse Toolkit Second Edition, Ralph Kimball writes the following: Rather than checking each field to see if something has changed, we instead compute a checksum for the entire row all at once. A cyclic redundancy checksum (CRC) algorithm helps us quickly recognize that a wide messy row has changed without looking at each of its constituent fields. On page 360 he writes: To quickly determine if rows have changed, we rely on a cyclic redundancy checksum (CRC) algorithm. If the CRC is identical for the extracted record and the most recent row in the master table, then we ignore the extracted record. We don't need to check every column to be certain that the two rows match exactly. People do sometimes use this logic in connection with much wider summary functions, such as an MD5 hash. I wouldn't trust it at all with a 32-bit CRC, and not much with a 64-bit CRC. Too much risk of collision. ---(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
[GENERAL] [OT] PHP vs Postgresql argument on Slashdot's front page.
Looks like the PHP vs Postgresql argument is on slashdot.org's front page. Just giving everyone a heads up so they can go and defend postgresql. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Stored Procedure performance / elegance question
x-no-archive:yes Hello. I have a stored procedure which returns a setof record. The function takes a few arguments, and if a couple of specific input values are null, it is required that the stored procedure perform different actions. I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. Which is better? fooA or fooB? : -- this one looks less elegant but is it faster because the planner stores the query? CREATE OR REPLACE FUNCTION fooA (value date , out myval) RETURNS SETOF RECORD $$ DEFINE rec RECORD; BEGIN IF value IS NULL THEN FOR rec IN SELECT * FROM test LOOP myval := rec.x RETURN NEXT; END LOOP; ELSE FOR rec IN SELECT * FROM test WHERE mydate $1 LOOP myval := rec.x RETURN NEXT; END LOOP; RETURN; END IF; END ; $$ LANGUAGE 'plgsql'; Here is fooB: --code looks cleaner especially when there are more null values to account for. Is it slower though? CREATE OR REPLACE FUNCTION fooB(value date , out myval) RETURNS SETOF RECORD $$ DEFINE rec RECORD; str varchar; BEGIN IF value IS NULL THEN str := SELECT * FROM test; ELSE str := SELECT * FROM test WHERE mydate ' || quote_literal($1); END IF; FOR rec IN EXECUTE str LOOP myval := rec.x RETURN NEXT; END LOOP; END ; $$ LANGUAGE 'plgsql'; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored Procedure performance / elegance question
Merlin Moncure wrote: On 8 Sep 2006 11:57:54 -0700, Karen Hill [EMAIL PROTECTED] wrote: I know that the planner does not store the plan when EXECUTE is used in a function, but the function looks better when the sql is created dynamically. my general rule is use static when you can, dynamic when you have to. this is a very trivial case which does not get into some of the problems with dynamic sql. however, if you are taking parameters that alter the actual structure of the query, dynamic might be appropriate. FOR rec IN SELECT * FROM test WHERE mydate $1 LOOP myval := rec.x RETURN NEXT; END LOOP; RETURN; END IF; you could of course do: FOR rec IN SELECT * FROM test WHERE $1 is null or mydate $1 loop [...] or some such. This was a simple example. In reality, the structure of the query is altered, but there are about 4 different query possibilities in the real problem depending on which values are null or not. My question was is it worth it to use Execute and suffer possible performance issues of having the planner make a new plan every time the Execute command was run? The alternative was to enumerate all 4 possible code execution paths in the store procedure using conditionals. I assume this is faster in execution but it looks ugly from a code point of view. also, you will get much better performance if you pass back a refcursor from the function instead of a setof record. return next is not advisable except for very small result sets. Don't refcursors consume a lot of database server resources? I wish to avoid that so in practice I use LIMIT and OFFSET to control results. ---(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] Insert Only Postgresql
Don't forget that one can create a DO NOTHING rules for DELETE and
UPDATE in addition to the INSERT only privilege. This will prevent
even the owner of the table from doing any accidental updating or
deleting.
Brandon Aiken wrote:
Sure. Any RDBMS can do that. Just create a user account (login role
for PostgreSQL) and only grant the INSERT privilege to them on your
tables, then connect with that account with your program. Any DELETE or
UPDATE statements will automatically fail.
--
Brandon Aiken
CS/IT Systems Engineer
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Solomon Asare
Sent: Friday, September 08, 2006 4:51 PM
To: [EMAIL PROTECTED]
Subject: [NOVICE] Insert Only Postgresql
Hi All,
pls, is there an Insert only version of postgreql or any other known
database? NO deletes, no updates. Inserts only! Any leads, please?
Best Regards,
solomon.
--_=_NextPart_001_01C6D38C.2205C945
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 4226
html xmlns:v=urn:schemas-microsoft-com:vml
xmlns:o=urn:schemas-microsoft-com:office:office
xmlns:w=urn:schemas-microsoft-com:office:word
xmlns:st1=urn:schemas-microsoft-com:office:smarttags
xmlns=http://www.w3.org/TR/REC-html40;
head
META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=us-ascii
meta name=Generator content=Microsoft Word 11 (filtered medium)
!--[if !mso]
style
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
/style
![endif]--o:SmartTagType
namespaceuri=urn:schemas-microsoft-com:office:smarttags name=PersonName/
!--[if !mso]
style
st1\:*{behavior:url(#default#ieooui) }
/style
![endif]--
style
!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:Times New Roman;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
--
/style
/head
body lang=EN-US link=blue vlink=purple
div class=Section1
p class=MsoNormalfont size=2 color=navy face=Arialspan style='font-size:
10.0pt;font-family:Arial;color:navy'Sure.nbsp; Any RDBMS can do that.
nbsp;Just create
a user account (login role for PostgreSQL) and only grant the INSERT privilege
to them on your tables, then connect with that account with your program.
nbsp;Any
DELETE or UPDATE statements will automatically
fail.o:p/o:p/span/font/p
p class=MsoNormalfont size=2 color=navy face=Arialspan style='font-size:
10.0pt;font-family:Arial;color:navy'o:pnbsp;/o:p/span/font/p
div
div
p class=MsoNormalfont size=2 color=navy face=Arialspan style='font-size:
10.0pt;font-family:Arial;color:navy'--/span/fontfont color=navyspan
style='color:navy'o:p/o:p/span/font/p
/div
div
p class=MsoNormalst1:PersonName w:st=onfont size=2 color=navy
face=Arialspan
style='font-size:10.0pt;font-family:Arial;color:navy'Brandon
Aiken/span/font/st1:PersonNamefont color=navyspan
style='color:navy'o:p/o:p/span/font/p
/div
div
p class=MsoNormalfont size=2 color=navy face=Arialspan style='font-size:
10.0pt;font-family:Arial;color:navy'CS/IT Systems
Engineer/span/fonto:p/o:p/p
/div
/div
div
div class=MsoNormal align=center style='text-align:center'font size=3
face=Times New Romanspan style='font-size:12.0pt'
hr size=2 width=100% align=center tabindex=-1
/span/font/div
p class=MsoNormalbfont size=2 face=Tahomaspan style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'From:/span/font/bfont size=2
face=Tahomaspan style='font-size:10.0pt;font-family:Tahoma'
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] bspan
style='font-weight:bold'On Behalf Of /span/bSolomon Asarebr
bspan style='font-weight:bold'Sent:/span/b Friday, September 08, 2006
4:51 PMbr
bspan style='font-weight:bold'To:/span/b [EMAIL PROTECTED]br
bspan style='font-weight:bold'Subject:/span/b [NOVICE] Insert Only
Postgresql/span/fonto:p/o:p/p
/div
p class=MsoNormalfont size=3 face=Times New Romanspan style='font-size:
12.0pt'o:pnbsp;/o:p/span/font/p
p class=MsoNormalfont size=3 face=Times New Romanspan style='font-size:
12.0pt'Hi All,br
pls, is there an Insert only version of postgreql or any other known database?
NO deletes, no updates. Inserts only! Any leads, please?br
br
Best Regards,br
solomon.o:p/o:p/span/font/p
/div
/body
/html
--_=_NextPart_001_01C6D38C.2205C945--
Re: [GENERAL] strange sum behaviour
Andrew Baerg wrote: Hi, I am getting strange results from the sum function as follows: corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; amount - 4.88 117.1 -121.98 (3 rows) corp=# select sum(amount) from acc_trans where trans_id=19721 and chart_id=10019; sum -- -1.4210854715202e-14 (1 row) amount is defined as double precision. I noticed that if I cast amount as numeric, the sum comes out 0 as expected. You are using the wrong datatype if you are working with currency. Use Numeric or Decimal instead. The money type is depreciated. http://www.postgresql.org/docs/8.1/interactive/datatype-money.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL:2003 Window Functions for postgresql 8.3?
Alvaro Herrera wrote: Karen Hill wrote: It would be really great if PostgreSQL supported SQL:2003 Window functions. I know that oracle and sql server have them already, so it would make postgres competitive in that area. I know there is a feature freeze for 8.2, is it doable for 8.3? The sooner you start writing a patch, the sooner you will be done ;-) I looked at the TODO list at http://www.postgresql.org/docs/faqs.TODO.html, and I don't see SQL:2003 Window Functions listed. Is it because they are not desired, or is it because there are more pressing things to accomplish? I noticed that Tom has mentioned that it appears unworkable in this thread. ---(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
[GENERAL] SQL:2003 Window Functions for postgresql 8.3?
I know that in pgsql.hackers they are discussing what to market the upcoming 8.2 release as. They mention updatable views, but realistically, PostgreSQL has had them via rules forever. I consider myself a database novice , and even I've created updatable views using rules quite easily. It would be really great if PostgreSQL supported SQL:2003 Window functions. I know that oracle and sql server have them already, so it would make postgres competitive in that area. I know there is a feature freeze for 8.2, is it doable for 8.3? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] money type depreciated?
I read in the documentation that the money type is depreciated. It says to use the to_char function and NUMERIC/decimal instead. Why was the money type depreciated when it was so useful? How would be the best way to use to_char and numeric to replace that type since I don't want to be using a depreciated data type. regards, ---(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
[GENERAL] PostgreSQL theoretical maximums.
How many tables and rows can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? What is an effective way to predict database size when designing tables? regards, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL theoretical maximums.
How many tables can PostgreSQL theoretically and then practically handle? What is the largest database size possible? What was the biggest database you've ever had on PostgreSQL? What were the challenges and what kind of hardware and OS works best? What is an effective way to predict database size when designing tables? regards, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] CREATE DATABASE question.
I have an sql file that doesn' t work properly when I do: psql mysql.sql . I cannot get it to connect to the database. Here what I'd like it to do: CREATE DATABASE testdb; \c testdb; CREATE TABLE tableTest(var varchar); But I get an error on the second line about an invalid character. Is it even possible to connect to a different db when giving an sql file to psql to process? regards, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is there a way to run tables in RAM?
Roy Souther wrote: I would like to know if there is anyway to move a section of some tables into RAM to work on them. I have large table, about 700MB or so and growing. I also have a bizarre collection of queries that run hundreds of queries on a small section of this table. These queries only look at about 100 or so records at a time and they run hundreds of queries on the data looking for patterns. This causes the program to run very slowly because of hard drive access time. Some times it needs to write changes back to the records it is working with. Is there anyway that I can move a few hundred records of the table into RAM and work on it there, it would be much faster. Is there anyway to create a temporary table that will only exist in RAM and not be written to the hard drive? Or do temporary tables already do that? If you are using linux, create a ramdisk and then add a Postgresql tablespace to that. regards, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] US Telephone Number Type
Hello, How would one go about creating a US telephone type in the format of (555)-555- ? I am at a loss on how it could be accomplished in the most correct way possible while not going into the various different country styles e.g. +01 (555) 555-. Is the difficulty of creating a telephone type the reason it is not in postgresql already? Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Or would regex be better? regards, ---(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] US Telephone Number Type
Tom Lane wrote: It doesn't seem particularly hard to make a type that stores just the digits (applying whatever amount of error-checking seems appropriate on the non-digit stuff it's throwing away) and on output regurgitates a standardized format. Minimum support would just be an input function and an output function, and it doesn't seem like you need too many other functions besides them I did a quick google and someone mentioned that input and output functions need to be written in C. Is that still the case? Anyway, there could be multiple number types to choose from such as: telephone-us-basic : (555) 555- telephone-us-extention : (555) 555- ext 1234 Other locals (EU, etc) could create their own to their local specifications. This would seem like a nice contrib package. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] A function which returns all rolname from pg_roles.
How do I make this function work? I am trying to get all the rolnames from pg_roles. CREATE OR REPLACE FUNCTION test() SETOF name AS $$ DECLARE rrol name; BEGIN SELECT rolname INTO rrol FROM pg_roles; RETURN setof rrol; END; $$ LANGUAGE plpgsql; regards, ---(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] How do I revoke CREATE TABLE and other privileges?
Michael Fuhr wrote: On Wed, Jul 05, 2006 at 02:27:19PM -0700, Karen Hill wrote: I would like for one role to be able to login, and execute a couple of functions and nothing else. I've tried to revoke access to CREATE on the database, schema, and tablespace but when I tested it, the user was still allowed to create tables. From the REVOKE documentation: Note that any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC. If PUBLIC still has privileges on the objects then the role still has privileges, even if you've attempted to revoke them. You'll probably need to alter the privileges that PUBLIC has, which might also require altering other roles' privileges to compensate. Hi, Revoking PUBLIC worked. I can now login to the database and it will not allow me to create new tables. However when I gave (as postgres) the restricted user permission to execute one function it says it cannot find the function when I try to execute it. regards, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Best way to deal with quote_literal issue?
Hello. I have client software that I wrote which uses parameters in function calls to postgresql. I use quote_literal in postgresql functions. That means I get data that is quoted when it finally ends up in the tables which I don't want. I know that you shouldn't trust data sent from the client, which is why I use quote_literal on the server side, and I also know using parameters is the best way to write client software which access an RDBMS. I don't want to remove the quote_literal just in case someone writes a new client and forgets to use parameters thereby exposing an SQL injection risk. Nor do I want to just keep quote_literal and dump using parameters. What is the best and most theoretically sound way to deal with this? regards, ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] How do I revoke CREATE TABLE and other privileges?
I would like for one role to be able to login, and execute a couple of functions and nothing else. I've tried to revoke access to CREATE on the database, schema, and tablespace but when I tested it, the user was still allowed to create tables. regards, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Is it possible to disable insert/update/delete triggers for one transaction and not another?
I have an insert/update/delete trigger on all my tables which add data to a log table. I would like to be able to disable them when the tables are called from one stored proceedure I have. Yet I would still like those triggers to fire on any other operation that is happening concurrently. Is this even possible? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?
I'm having a bit of mystery in solving a postgresql puzzle. I have a table that when it gets inserted or updated or deleted it is logged into a log table. The log table contains who (current_user) did the insert/update/delete the CURRENT_TIMESTAMP. Everything works great except the INSERT because I cannot use OLD and NEW increments the serial twice! CREATE TABLE ttest (bpchar, instime abstime, prikey serial PRIMARY KEY); CREATE TABLE ttest_log ( value bpchar, user bpchar, instime abstime, modtime abstime , logprikey int4); CREATE RULE ri AS ON INSERT TO ttest DO INSERT INTO ttest_log (NEW.value , current_user, CURRENT_TIMESTAMP, 'infinity', NEW.logprikey); --on the above NEW.logprikey creates two different primary keys!! One pk for the ttest and pk +1 for ttest_log! CREATE RULE rupd AS ON UPDATE TO ttest DO INSERT INTO ttest_log (old.value, current_user, old.instime, CURRENT_TIMESTAMP); CREATE RULE rdel AS ON DELETE TO ttest DO INSERT INTO ttest_log (old.value, current_user, old.instime, CURRENT_TIMESTAMP); ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] top predicate
It seems PostgreSQL doesn't have a TOP Predicate. Why is that? Here is an example: SELECT TOP 10 products from sales; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] top predicate
Tom Lane wrote: Karen Hill [EMAIL PROTECTED] writes: It seems PostgreSQL doesn't have a TOP Predicate. Why is that? It's not in the SQL standard. If we were to implement something like what I think you're asking for (your example is way underspecified), it'd probably look like SQL2003's window functions. Hi Tom, The TOP predicate seemed to be really common in some other RDBMS. I guess it isn't in the standard since oracle seems to be missing it in 9i. Maybe 10g has it. http://www.oracle.com/technology/tech/migration/ama/exchange/docs/ss2k/SELECTStatement.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] top predicate
Jan de Visser wrote: On Thursday 11 May 2006 16:34, Karen Hill wrote: It seems PostgreSQL doesn't have a TOP Predicate. Why is that? Here is an example: SELECT TOP 10 products from sales; Just for my understanding: This would return the 10 products with the most matching sales rows, right? jan No, it would return the top 10 selling products in this example. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Preventing SQL Injection in PL/pgSQL in psql
Is my understanding correct that the following is vulnerable to SQL injection in psql: CREATE OR REPLACE FUNCTION fx ( my_var bchar) RETURNS void AS $$ BEGIN INSERT INTO fx VALUES ( my_var ) ; END; $$ LANGUAGE 'plpgsql' VOLATILE Where this is NOT subject to SQL injection: CREATE OR REPLACE FUNCTION fx ( my_var bpchar) RETURNS void AS $$ BEGIN EXECUTE ' INSERT INTO fx VALUES ( ' || quote_literal( my_var) || ' ); ' END; $$ LANGUAGE 'plpgsql' VOLATILE Is this understanding correct? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] What is your favorite front end for user interaction to postgresql databases?
What is your favorite front end for end users to interact with your postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on rails? Why is it your favorite? Which would you recommend for end users on multiple OSes? Also, what do you think of having the database management system do all work (business logic etc) with the front end as a user interface vs. having N-tier with an application server handling business logic, and the db just accepting data. I currently have the opinion that N-tier is not as good as having the db contain the business logic via stored procedures. This comes from having to re-invent the wheel every time a new user app needs to be created. ---(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
[GENERAL] Is an updateable/insertable recordset via ADO possible using MS-Access?
Hello. I have an MS - Access front end which connects to PostgreSQL 8.1.3. Almost everything is working great, I especially love how I can use rules in PostgreSQL to be able to update and insert into views, which is awesome. Now my only issue, and I'm not sure this is even possible but here it is. Can one base an MS Access form on a PostgreSQL table using only ADO and have it be updateable and insertable? Currently all I'm able to do is get the data as READONLY when I do it via ADO, yet when I use linked tables in MS Access I am able to update and insert. Here is the ADO code: Sub Form_Open ( Cancel As Integer) Dim cn As ADODB.Connection Dim rs AS ADODB.RecordSet Set cn = New ADODB.Connection Set rs = New ADODB.RecordSet cn.Open DSN=PostgreSQL ANSI; Database=db; UID=postgres; Password=; rs.CursorLocation = adUseServer rs.Open SELECT * FROM view_x;, cn, adOpenKeySet, adLockOptimistic Set Me.Recordset = rs End Sub The above returns records to the form, its just that they are not updateable or insertable. Thanks for your help. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] insert into a view?
Tom Lane wrote: I hope it said rules, because you can't put a trigger on a view. regression=# create table t(f1 int, f2 text); CREATE TABLE regression=# create view v as select * from t; CREATE VIEW regression=# insert into v values(22, 'foo'); ERROR: cannot insert into a view HINT: You need an unconditional ON INSERT DO INSTEAD rule. regression=# create rule r as on insert to v do instead regression-# insert into t values(new.*); CREATE RULE regression=# insert into v values(22, 'foo'); INSERT 0 1 regression=# select * from t; f1 | f2 +- 22 | foo (1 row) Thanks Tom, I tried it and it worked. Is it possible to do something a bit more complex? Can you use rules to insert into a view that has multiple tables as the source? For example: CREATE VIEW v AS SELECT * FROM t1, t2 WHERE t1.num = t2.num; Would the rule for the above look something like this? CREATE RULE r AS ON INSERT INTO t1, t2 WHERE t1.num = t2.num DO INSTEAD INSERT INTO t1 , t2 VALUES (new.*); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] insert into a view?
Tried it but didn't work. It gave me a hint though to try triggers. Can anyone show me how to do an insert into a view using triggers? Thanks. :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.
Cool. I knew ADO could do transactions on Access's JET database engine, but didn't know they could do so on another RDBMS like PostgreSQL. So basically I can use the BeginTrans and CommitTrans to do the work of PostgreSQL's BEGIN; and COMMIT; On a adjacent topic, how does PostgreSQL know that BeginTrans and CommitTrans are psuedonyms for BEGIN and COMMIT? Is it the ODBC driver? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Transactions, PostgreSQL and MS Access front end.
From Access I'd like to run pass the following from MS Access to PostgreSQL 8.1 using VBA: BEGIN; UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345; UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534; COMMIT; It won't let me. Any ideas solutions? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Transactions, PostgreSQL and MS Access front end.
Bruce Momjian wrote:
Karen Hill wrote:
From Access I'd like to run pass the following from MS Access to
PostgreSQL 8.1 using VBA:
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;
It won't let me. Any ideas solutions?
What error does it show?
Error on character 7.
I suspect it only allows one SQL statement to go through and thinks
everything after the BEGIN; is an error. I thought of creating a
function in pl/pgsql that would allow me to do this. Something like
this in postgresql: NOTE:pseudocode
function(sql_statement_1, sql_statement_2){
BEGIN;
sql_statement_1;
sql_statement_2;
COMMIT;
}
And then I'd run that function from access: NOTE:pseudocode
DoCmd.RunSQL function('UPDATE accounts..WHERE acctnum = 12345',
'UPDATE accounts...WHERE acctnum = 7534')
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
