Re: [SQL] transaction management in plpgsql functions
http://www.postgresql.org/docs/7.2/interactive/plpgsql-structure.html "It is important not to confuse the use of BEGIN/END for grouping statements in PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a transaction. Functions and trigger procedures are always executed within a transaction established by an outer query --- they cannot start or commit transactions, since PostgreSQL does not have nested transactions." Makes sense if you think about it. On Thu, 6 Nov 2003, Cris Carampa wrote: > It seems that transaction management statements (begin work...commit > work) are not allowed into plpgsql functions. Is it true? If true, what > happens if I put a DML statement into a function? Is it automatically > commited every time the function executes? Is there no way to rollback > the changes? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Quota query with decent performance?
maybe: select * from person where age <= (select age from person order by age limit 1 offset 2); 7.20 msec assuming it does what you want. On Tue, 11 Nov 2003, Troels Arvin wrote: > An example of a quota query could be to get the top-3 youngest people from > a collection of people. The complicated part is that such a query might > return more than 3 rows in some tie situations. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] increment int value in subset of rows?
here is a work-a-round: # create table t (a int, primary key (a)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 't_pkey' for table 't' CREATE # insert into t values (1); # insert into t values (1); ERROR: Cannot insert a duplicate key into unique index t_pkey # insert into t values (2); INSERT 5110301 1 # insert into t values (3); INSERT 5110302 1 # update t set a = a+1; ERROR: Cannot insert a duplicate key into unique index t_pkey # update t set a = -a; UPDATE 3 # update t set a = -a + 1; UPDATE 3 # select * from t; a --- 2 3 4 (3 rows) if i remember correctly, sql for smarties book has an item on this. On Sun, 23 Nov 2003, george young wrote: > This doesn't work, since the *order* of execution of these updates > is not guaranteed, and I actually would need to start with the highest > value of seq and work down. There may be a thousand or so rows for 'foo' > run, so an external loop of queries would be very expensive. > How can I increment all the seq values for foo columns where seq > something? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question about isolation
On Wed, 28 Jan 2004, Samuel Tardieu wrote: > If in a transaction I call an embedded function in Pl/PgSQL, in which > I have: > > delete from t where condition; > for e in select distinct on (f) * from t where ... loop > ... > end loop; > > Do I have the guarantee that, in any event, rows deleted from table t > by the delete won't reappear in the select result? i do not think you have that guarantee in READ COMMITTED mode because there is a slight possibility another backend sneaked a committed insert in between the delete and select statement. perhaps you want to change to SERIALIZABLE transaction isolation. or perhaps you would like to repeat the WHERE condition from the DELETE in the following SELECT so as to not gather any of the offending rows. http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about isolation
On Wed, 28 Jan 2004, Chester Kustarz wrote: > On Wed, 28 Jan 2004, Samuel Tardieu wrote: > > If in a transaction I call an embedded function in Pl/PgSQL, in which > > I have: > > > > delete from t where condition; > > for e in select distinct on (f) * from t where ... loop > > ... > > end loop; > > > > Do I have the guarantee that, in any event, rows deleted from table t > > by the delete won't reappear in the select result? > > i do not think you have that guarantee in READ COMMITTED mode because > there is a slight possibility another backend sneaked a committed insert in > between the delete and select statement. perhaps you want to > change to SERIALIZABLE transaction isolation. or perhaps you would > like to repeat the WHERE condition from the DELETE in the following > SELECT so as to not gather any of the offending rows. > > http://www.postgresql.org/docs/7.4/static/sql-set-transaction.html perhaps the isolation level applies to the statement that called the function, in which case you would be ok. that would make more sense, no? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] move forward 0 from foo;
I expected "MOVE FORWARD 0 FROM foo;" to always return 0, but I have found this not to be the case. Could anybody comment whether this is expected: mow=# begin; BEGIN mow=# create table a (a integer); CREATE TABLE mow=# insert into a values ( 1 ); INSERT 1823482 1 mow=# insert into a values ( 1 ); INSERT 1823485 1 mow=# declare foo cursor for select * from a; DECLARE CURSOR mow=# move forward 0 from foo; MOVE 0 mow=# fetch forward 1 from foo; a --- 1 (1 row) mow=# move forward 0 from foo; MOVE 1 mow=# move forward 1 from foo; MOVE 1 mow=# fetch forward 1 from foo; a --- (0 rows) mow=# move forward 0 from foo; MOVE 0 mow=# select version(); version - PostgreSQL 7.4.1 on i386-unknown-openbsd2.8, compiled by GCC 2.95.3 (1 row) Basically I found it odd that the MOVE FORWARD 0 in the middle returned 1. Of course I can avoid the MOVE FORWARD 0 in my application logic, but it meant special casing the instance for 0. The docs don't seem to be too verbose on the subject: http://www.postgresql.org/docs/7.4/interactive/sql-move.html On successful completion, a MOVE command returns a command tag of the form MOVE count The count is the number of rows moved over (possibly zero). I can see where the difference might originate, but it seems strange that MOVE FORWARD 0 and MOVE FORWARD 1 could both return "1" so long as the cursor points to something. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] COUNT(*) to find records which have a certain number of
On Mon, 20 Sep 2004, T E Schmitz wrote: > I was feeling a bit guilty about posting such a trivial question. I can > cobble together some straightforward SQL but I could really do with a > source of more complex SQL examples. > If you know of any links - that would great and save the list from more > such questions ;-) SQL for Smarties has some more complicated examples and topics for "advanced" type queries. I can't say it's exhaustive, but I found it a good bridge by hinting at what is really possible. I also found that a good way to improve is to try to write every complicated query by using all the different ways I can think of, like: - UNION (ALL) - SUB-SELECT - LEFT OUTER JOINS - HAVING etc. Here is the link for SQL for Smarties: http://www.amazon.com/exec/obidos/tg/detail/-/1558605762/002-957-7220055?v=glance The bad thing about the book is that it is sort of SQL agnostic, so some of the examples would be sub-optimal on postgresql, or may not even work. I would like to hear about other sources too. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] JOIN performance
On Mon, 20 Sep 2004, Dean Gibson (DB Administrator) wrote: > Okay, now for my big question: I searched high and low for a function that > would return the minimum of two dates, and found none. Now you come up > with "date_smaller", which works fine (as does "date_larger"), but where > are those documented? More importantly, where are other functions like > them documented? You can make them: http://www.postgresql.org/docs/7.4/interactive/server-programming.html "strict" means it will return NULL when the input is NULL. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] select column by position
On Fri, 24 Sep 2004, Jennifer Lee wrote: > Is there a way to select a column in a table by its position rather than > the field name? If I understand correctly column position in a table is > fixed. I've not been able to find anything in the archives or docs to > indicate that it's possible to select by position. The general response will be that you don't really want to do this. You should figure out some other way to query the database that doesn't rely on column position. For example, dropping and adding columns with ALTER TABLE command could change their positions. If you still want to go about it, I suppose it might be possible to write a PL/pgSQL stored procedure that determines the column name from the system catalogs, then uses EXECUTE to run the query with the real column name substituted in the query. See Executing Dynamic Commands in the documentation: http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Documentation about what is stored in the system catalogs can be found: http://www.postgresql.org/docs/7.4/interactive/catalogs.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
