[SQL] functions and triggers
I'm trying to build a trigger that will update a timestamp field in a table with the current timestamp, and I just can't make it work. The problemas are two: 1) when I try to create the trigger, it says that the function doesn't exist. Why is this happening? 2) How does the trigger tell the function the row number identifier? I'm really stuck with this. Saludos... :-) -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(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] functions and triggers
Quoting Stephan Szabo <[EMAIL PROTECTED]>: > > > The problemas are two: > > > > 1) when I try to create the trigger, it says that the function doesn't > exist. > > Why is this happening? > > You should probably show us what you were trying to do, but I'm going to > guess that the function doesn't have the right signature. On > current versions, Trigger functions should return opaque and take no > arguments (any arguments given on the create trigger line are passed > in a different fashion). I have this function which works OK. CREATE FUNCTION ahora (integer) RETURNS integer AS ' UPDATE usuarios SET tmodif = now() WHERE codigo = $1; SELECT 1 as RESULT; ' LANGUAGE SQL > > 2) How does the trigger tell the function the row number identifier? > > I'm not sure what you mean by this. Getting at the row being worked on > depends somewhat on what language you're using. I thought about a simple SQL that does the update. You mean I just call the function from the trigger and thats all? -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to vacum
El Mié 10 Sep 2003 10:07, Bruno Wolff III escribió: > On Wed, Sep 10, 2003 at 20:43:25 +0800, > > Richard Sydney-Smith <[EMAIL PROTECTED]> wrote: > > Tried to issue the command "vacum full" both from psql and the sql box in > > pgadmin without success. > > > > How do you use the command? > > > > select vacum full; > > > > also does not work > > Try using: > vacuum full > (Note that vacuum has 2 u's.) When entering "vacum" to Dict, it catches nothing, but one of it's suggestions is vacuum, which means "clean". -- 17:38:01 up 19 days, 9:28, 3 users, load average: 1.59, 1.39, 0.91 - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(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] auto_increment
El Sáb 20 Sep 2003 03:23, Oliver Elphick escribió: > On Sat, 2003-09-20 at 06:10, Muhyiddin A.M Hayat wrote: > > How to Create auto_increment field in PostreSQL. > > Can I create them using Trigger. > > Use the SERIAL datatype. See also the functions nextval(), currval() > and setval(). Also to add, the auto increment is done through sequences. I would suggest seeing the docs on SEQUENCE, and SERIAL data type. -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] auto_increment
Why do you want it to rollback? El Sáb 20 Sep 2003 05:43, Muhyiddin A.M Hayat escribió: > Ok, but if i do rollback, the auto_increment don't roolback. > How to use nextval(), currval() and setval() functions. > > - Original Message - > From: Cavit Keskin > To: 'Muhyiddin A.M Hayat' > Sent: Saturday, September 20, 2003 2:15 PM > Subject: RE: [SQL] auto_increment > > > Create table tablename( > > id serial, > > > > ); -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(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
[SQL] defaults on updates
Hi, I'm trying to make some sort of function, rule, trigger, or what ever that would be capable of modifing my table on an update. Basically, I have 5 fields, one being a PK (SERIAL), 3 with information, and the last one a timestamp field that will show the last time the register was modified (tmodif which has a DEFAULT CURRENT_TIMESTAMP). Now, eveytime someone modifys any, or all of the 3 information fields, I want the tmodif field of that register to be set to now(). I tried with rules, but just didn't work (obviously, and not so), even with INSTEAD. The only way I see of doing it is through a view/rule, but I would leave that as the last resource. Any ideas? -- 10:43:01 up 10 days, 18:05, 4 users, load average: 0.95, 0.44, 0.35 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Anti log in PostgreSQL
El Vie 26 Dic 2003 19:12, Sai Hertz And Control Systems escribió: > Dear all , > > In one of our project I require to calculate antilog of (3.3234) > But I could not find any functions in Documentation for the same. > > In mathematics I would have written it something like > > A = antilog (3·3234) = 2144 As I can understand, this is a 10 base log, so that what you want is 10^(3.3234)? For that you have the exponential operator ^. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(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] Anti log in PostgreSQL
El Vie 26 Dic 2003 19:46, Sai Hertz And Control Systems escribió: > Dear Martin Marques , > > >>In mathematics I would have written it something like > >> > >>A = antilog (3·3234) = 2144 > >> > >> > > > >As I can understand, this is a 10 base log, so that what you want is > >10^(3.3234)? > > > >For that you have the exponential operator ^. > > > > > Nope > select exp(3.3234) as a2144 > Gives me > 27.754555808589792 > But the answer expected is > some what near to 2144 > The log tables show this As I said, log10, not natural logaritm prueba=> select 10^3.3234 AS res; res -- 2105.71698391175 (1 row) Octave gives me this: octave:1> 10^3.3234 ans = 2105.7 What is 2144? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(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
[SQL] select by groups
I have a table with names of people, email address, etc, and an identifier that tells me which group they are in (could be a 1, 2, or 3 person group). Is it posible to make a query that would give me the name of the persons of each group in one row? Or do I have to do PL? -- 19:15:01 up 97 days, 1:24, 4 users, load average: 0.00, 0.07, 0.17 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] inverse of "day of year"
Is there a function that would give me the date for a given day of year? Something like the inverse of "EXTRACT(doy FROM date)"? -- 09:04:02 up 10 days, 13:35, 4 users, load average: 0.42, 0.29, 0.33 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] psql: FATAL 1: IDENT authentication failed for user error - Urgent pls
El Vie 19 Mar 2004 09:39, Kumar escribió: > Dear Friends, > > I have installed Linux Fedore and wanted to work with the default installed > postgres 7.3.4 database. > > I could able to create to create user, but while try to connect, I got the > following error message psql: FATAL 1: IDENT authentication failed for user > > My pg_hba.conf file looks like below > > # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD > > local all all trust > host all all 192.168.2.0 255.255.255.0 trust > > I couldnt understand why. Please shed some light This is not the default configuration, so I suspect that you changed it. Try to reload PostgreSQL and try again. -- 10:04:02 up 10 days, 14:35, 4 users, load average: 0.27, 0.24, 0.27 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] inverse of "day of year"
El Lun 22 Mar 2004 09:50, escribió:
>
> Intervals are stored as two components. One is absolute time difference,
> and the other is in months. '1 year' is equivalent to '12 months'.
> The documentation on how they work in corner cases (when added or
> subtracted from timestamp(tz)) is sparse. It isn't documented whether the
> part in months or the absolute time is added first or what timezone is used
> (for
> timestamptz) when adding the months part.
> The basic idea is that months are added by looking at the timestamp
> as date and time and adding the appropiate number of months to the date
> and then converting back to a timestamp. It isn't documented what happens
> when the day of the month is past the end of the new month, but it looks
> like the last day of new month is used.
> If you convert an interval to an absolute time (such as by extracting the
> epoch), then months are converted to 30 days. Again, I don't think this
> is documented.
Any thoughts on how this could affect date manipulation?
mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
'years')::interval;
?column?
-
2005-02-28 00:00:00
AFAIKS with other dates this works OK. :-)
--
10:11:02 up 13 days, 14:42, 4 users, load average: 0.17, 0.12, 0.16
-
Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] inverse of "day of year"
El Lun 22 Mar 2004 12:56, Dana Hudes escribió: > If you have the option to handle the date manipulation in Perl > use the DateTime modules. Also see Date::Calc. NO! Actualy what I'm doing is getting out of that (I'm using PHP's PEAR Date::Calc) by creating some nice SQL and PL/PgSQL functions in the DB server. -- 11:01:02 up 14 days, 15:32, 4 users, load average: 1.48, 1.11, 0.72 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Standatd
El Mié 07 Abr 2004 11:26, escribió: > Ricardo Vaz Mannrich wrote: > > Thank you. > > > > I think there is an error in the Develpoer's FAQ... > > > > http://developer.postgresql.org/ > > http://developer.postgresql.org/readtext.php?src/FAQ/FAQ_DEV.html+Develop > >ers-FAQ > > > > A lot of ugly characters. > > Until we fix the web site, please see doc/src/FAQ/FAQ_DEV.html in the > source tree. http://db.konkuk.ac.kr/present/SQL3.pdf doesn't exist anymore. Also, for some reason I can't get to sqlstandards.org. Is there any other place where I can get the SQL200X docs? -- 18:11:01 up 29 days, 22:38, 2 users, load average: 0.56, 0.53, 0.43 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problem porting MySQL SQL to Postgres
El Jue 15 Abr 2004 07:25, Dan Field escribió: > I've stumbled across a query I don't quite understand the error message > for. > > This query is pulled from a working MySQL setup: > > SELECT > DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, > DEWEY_POINT_TENS, > DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS,DEWEY_TYPE, DEWEY_LANG, > DEWEY_SUBJECT FROM lu_dewey > WHERE > (DEWEY_HUNDREDS = 9) AND > (DEWEY_TENS >= 0) AND > (DEWEY_TENS <= 9) AND > (DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND > (DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND > (DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND > (DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND > (DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND > (DEWEY_TYPE = 't') AND > (DEWEY_LANG = 'en') > ORDER BY DEWEY_TENS > > > However I'm getting the following error: > > ERROR: Unable to identify an operator '=' for types 'character' and > 'boolean' You will have to retype this query using an explicit cast. 1) Change the "= NULL" to "IS NULL" which is how it's defined in the SQL standards. 2) What data type does DEWEY_TYPE have? -- 08:28:01 up 37 days, 12:55, 2 users, load average: 0.72, 0.77, 0.90 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Can someone tell me why this statement is failing?
El Lun 19 Abr 2004 17:31, P A escribió: > Can anyone tell me why this SQL statement is not > matching with the row from the table below? I think > I'm going mad! > > # > SQL Statement > # > > SELECT * FROM t_bell_schedule WHERE calendar_day = > '2004-04-12' AND start_time_minutes >= '1082374200' > AND end_time_minutes <= '1082375100'; start_time_minutes and end_time_minutes are integer data types, so don't enclose the values in quotes. > start_time_minutes | integer | > end_time_minutes | integer | -- 11:38:01 up 42 days, 16:05, 4 users, load average: 0.48, 0.61, 0.55 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] CONTEXT on PL/pgSQL
I have a simple function made with PL/pgSQL and when I call it I get this in the logs: 2004-04-23 10:15:32 [30669] LOG: statement: SELECT nodoSuperior(22) AS sup 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 CONTEXT: PL/pgSQL function "nodosuperior" line 7 at assignment 2004-04-23 10:15:32 [30669] LOG: statement: SELECT codigo,padre,nombre FROM procesos WHERE codigo= $1 CONTEXT: PL/pgSQL function "nodosuperior" line 10 at select into variables 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 IS NULL CONTEXT: PL/pgSQL function "nodosuperior" line 12 at exit 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 CONTEXT: PL/pgSQL function "nodosuperior" line 16 at return What does CONTEXT mean, and is everything ok? The function is this: CREATE OR REPLACE FUNCTION nodoSuperior(INT) RETURNS VARCHAR AS ' DECLARE COD INT; SUP RECORD; BEGIN COD:=$1; LOOP SELECT INTO SUP codigo,padre,nombre FROM procesos WHERE codigo=COD; EXIT WHEN SUP.padre IS NULL; COD:=SUP.padre; END LOOP; RETURN SUP.nombre; END; ' LANGUAGE 'plpgsql'; -- 10:16:01 up 45 days, 14:40, 3 users, load average: 0.54, 0.61, 0.63 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CONTEXT on PL/pgSQL
El Vie 23 Abr 2004 11:18, Tom Lane escribió: > Martin Marques <[EMAIL PROTECTED]> writes: > > I have a simple function made with PL/pgSQL and when I call it I get this > > in the logs: > > > > 2004-04-23 10:15:32 [30669] LOG: statement: SELECT nodoSuperior(22) AS > > sup 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 > > CONTEXT: PL/pgSQL function "nodosuperior" line 7 at assignment > > log_statement currently logs everything the parser sees, which includes > SQL commands generated by plpgsql. > > Arguably these facilities should be separated, but until someone > makes a serious effort to provide plpgsql debugging features, > it's likely that nothing will be done about it. Right now this is > almost the only technique available for seeing what's going on inside > a plpgsql function, and crummy as it is, it's better than nothing... So the CONTEXT line just tells where the statement was made? -- 12:06:01 up 45 days, 16:30, 2 users, load average: 0.50, 0.46, 0.45 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] colored PL with emacs
Does anyone know of a .el file that can be used with Emacs to get colored coding when working with PL/pgSQL? -- 08:40:01 up 8 days, 27 min, 1 user, load average: 2.53, 2.09, 1.70 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] colored PL with emacs
El Mar 31 Ago 2004 09:11, Stephen Quinney escribió: > On Tue, Aug 31, 2004 at 08:42:44AM -0300, Martin Marques wrote: > > Does anyone know of a .el file that can be used with Emacs to get colored > > coding when working with PL/pgSQL? > > Emacs 21.3 (and possibly earlier versions) comes with an SQL mode > which I know has a PostgreSQL keyword highlighting option in the > menu. Just just need: > > Meta-x sql-mode > Meta-x global-font-lock-mode > > (and possibly select the postgres option in the highlighting bit of > the SQL menu that appears if you are using X) I have SQL highlighting, but what I want are colors for the PL/pgSQL key words. It would make PL programming much easier. -- 09:30:02 up 8 days, 1:17, 2 users, load average: 0.70, 0.44, 0.54 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Best way to know if there is a row
I have a bunch of queries in a system I'm finishing, and I bumped with a question on performace. Which is the best way to solve this: I need to know if there is at least one row in the relation that comes from a determinated query. Which is the best way to do this: (a) Build the query with "SELECT * ..." and after executing the query see if numRows()>0 (b) Build the query with "SELECT count(*) ...", fetch the row and see if count>0 I'm working with (a) because I see it better in performace, but I wanted to be sure the numRows() will actually give me the exact amount of rows (had some problems in the past with Informix). The aplication is written in PHP. -- 09:45:02 up 16 days, 3 min, 4 users, load average: 3.32, 2.69, 1.77 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHECK col A not NULL if col B='x'
El Mar 28 Sep 2004 11:02, T E Schmitz escribió: > Hello, > > Is it possible to set up a table CHECK, which ensures that column A is > NOT NULL if column B = 'x' ? CONSTRAINT constraint_name ] CHECK (expression) CHECK (expression) The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint may reference multiple columns. So I would say that it should be: CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL) (use a logical table to build the correct logical expression) -- 11:05:01 up 16 days, 1:23, 4 users, load average: 1.26, 0.70, 1.04 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Sun, 18 Dec 2005, frank church wrote: Does VACUUMing reorder tables on clustered indices or is it only the CLUSTER command that can do that? Cluster does that. Vacuum only cleans dead tuples from the tables. -- 18:02:25 up 4 days, 9:57, 5 users, load average: 1.59, 1.57, 1.62 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática|'@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(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: [SQL] Slightly confused error message
On Fri, 28 Apr 2006 12:07:04 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > Markus Schaber <[EMAIL PROTECTED]> writes: >> I just stumbled over a slightly confused error message: > >> mydb=# select count(*),coverage_area from myschema.streets except select >> cd as coverage_area from countryref.disks group by > streets.coverage_area; >> ERROR: column "streets.coverage_area" must appear in the GROUP BY >> clause or be used in an aggregate function > >> As the query looks, streets.coverage_area is actually used in the GROUP > BY. > > The complaint is 100% accurate; the subquery that it's unhappy about is > > select count(*),coverage_area from myschema.streets > > which is an aggregating query, but coverage_area is being used outside > an aggregate without having been grouped by. I see lack of parenthesis in the sub-query: select count(*),coverage_area from myschema.streets except (select cd as coverage_area from countryref.disks) group by streets.coverage_area; -- - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Rows with exclusive lock
Is it posible to get an exclusive (read/write) lock on certain rows? I don't want to block the whole table, only certain rows, but I want it to be a read/write lock. AFAIK SELECT FOR UPDATE doesn't help with this. Do I have to go for another aproche? -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(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: [SQL] Rows with exclusive lock
On Sat, 22 Jul 2006, Alvaro Herrera wrote: Martin Marques escribió: Is it posible to get an exclusive (read/write) lock on certain rows? I don't want to block the whole table, only certain rows, but I want it to be a read/write lock. That's what SELECT FOR UPDATE does. Hi Alvaro, After the SELECT FOR UPDATE other transactions can still see the locked rows. I want a read/write lock, so no one can access does rows. AFAIK SELECT FOR UPDATE doesn't help with this. Why? trans1: prueba2=> BEGIN; BEGIN prueba2=> SELECT * FROM personas FOR UPDATE; codigo | nombre | apellido | tipodoc | docnum ++--+-+------ 3 | Martin | Marques | 1 | 23622139 (1 row) Meanwhile, at this moment trans2: prueba2=> BEGIN; BEGIN prueba2=> SELECT * FROM personas; codigo | nombre | apellido | tipodoc | docnum ++--+-+------ 3 | Martin | Marques | 1 | 23622139 (1 row) pg_locks shows the the lock is RowShareLock, so there is no read lock on those rows, which is what I want. -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Rows with exclusive lock
On Sun, 23 Jul 2006, Alvaro Herrera wrote: Martin Marques escribió: After the SELECT FOR UPDATE other transactions can still see the locked rows. I want a read/write lock, so no one can access does rows. SELECT FOR UPDATE acquires an exclusive lock, but other transactions must try to acquire a lock on the rows as well, or they won't be locked. You can try using SELECT FOR SHARE (new as of 8.1) if you want some transactions to hold shared (read) locks. Sorry for not getting it clear the first time. What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but at row level. IOW, SELECT FOR UPDATE blocks other SELECTs FOR UPDATE and SELECTs FOR SHARE, but it does not block plain SELECT. So, this is not posible. :-( -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] age() vs. timestamp substraction
I just found this problem with the age() function, which AFAIK should give the same resulte as a subtraction of the argument from now(), but it doesn't. prueba=> SELECT (now() - tc.last_cron),age(tc.last_cron),tc.intervalo FROM tareas_cron tc ; ?column? | age | intervalo -+---+--- @ 1 day 15 hours 13 mins 12.06 secs | @ 23 hours 59 mins 58.47 secs | @ 1 day @ 15 hours 13 mins 12.06 secs | @ 1.52 secs ago | @ 30 mins @ 15 hours 13 mins 12.08 secs | @ 1.50 secs ago | @ 10 mins prueba=> SELECT version(); version PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 (Debian 4.0.3-1) Any ideas on why? I starting to change my queries so they don't have the age() function anymore. -- - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] age() vs. timestamp substraction
On Thu, 05 Oct 2006 14:37:24 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > Martin Marques writes: >> I just found this problem with the age() function, which AFAIK should >> give the same resulte as a subtraction of the argument from now(), > > Where did you get that idea? age's reference point is current_date (ie, > midnight) not now(). There are also some differences in the calculation > compared to a plain timestamp subtraction. Ignore anything I said. Just realized it said current_date. :-( Sorry. -- - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Problems with temp table and PL
I create a temp table inside a plpgsql function, which is drop just before ending (the function). My problem is that if I execute the same function again (in the same session) I get an error when trying to insert data into it (looks like the session has an old reference of the table): => SELECT actualizacionAnualProximoHabil(2008); ERROR: relation with OID 9668312 does not exist CONTEXT: SQL statement "INSERT INTO dias_semana VALUES ( $1 )" PL/pgSQL function "actualizacionanualproximohabil" line 9 at SQL statement As I said, the first execution works OK, but from then on it gives this error, until I close the session and open it again. Any ideas? -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] triple self-join crawling
T E Schmitz escribió:
QUERY PLAN
GroupAggregate (cost=1370368.19..1435888.88 rows=5801 width=56) (actual
time=11945.030..13163.156 rows=5801 loops=1)
-> Sort (cost=1370368.19..1379715.86 rows=3739067 width=56) (actual
time=11944.753..12462.623 rows=120117 loops=1)
Sort Key: history.stock, history."day", history.high, history.low
-> Hash Left Join (cost=160.02..391554.63 rows=3739067
width=56) (actual time=52.746..3778.409 rows=120117 loops=1)
Hash Cond: ((("outer".stock)::text =
("inner".stock)::text) AND ("outer"."day" = "inner"."day"))
Join Filter: ("inner"."day" >= ("outer"."day" - 7))
-> Nested Loop Left Join (cost=0.00..204441.26
rows=3739067 width=57) (actual time=0.077..2313.375 rows=120117 loops=1)
It's estimating >3M, but it finds 120K rows.
Join Filter: (("inner".stock)::text =
("outer".stock)::text)
-> Seq Scan on history (cost=0.00..131.01
rows=5801 width=34) (actual time=0.016..34.845 rows=5801 loops=1)
-> Index Scan using idx_history_day on history
past_month (cost=0.00..22.32 rows=645 width=23) (actual
time=0.020..0.185 rows=21 loops=5801)
Index Cond: ((past_month."day" >=
("outer"."day" - 30)) AND (past_month."day" < "outer"."day"))
-> Hash (cost=131.01..131.01 rows=5801 width=23) (actual
time=52.608..52.608 rows=5801 loops=1)
-> Seq Scan on history past_week (cost=0.00..131.01
rows=5801 width=23) (actual time=0.010..25.110 rows=5801 loops=1)
Total runtime: 13187.729 ms
Try running a vacuum analyze on the database (or at least the tables
which differ in rows estimated and actual (history for example))
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-
Martín Marqués | Programador, DBA
Centro de Telemática| Administrador
Universidad Nacional
del Litoral
-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Select and Count
On Tue, 20 Mar 2007, Shavonne Marietta Wijesinghe wrote:
Hello
I have a postgresql table and i do a select via ASP
strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND
TRIM(date_inserted) <= '" & datafine & "'"
oRs.open strSQL,oConn,3
schede = oRs.RecordCount
Do until oRs.EOF
sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
oRs.movenext
Loop
I know nothing about ASP, but it looks like you are doing a SUM of an int
with, maybe, an array (don't know how ASP defines oRs().Value output).
Keep in mind that you are pulling all the columns of that table (as you
used a * in the column selection).
Just a guess, nothing more then that.
--
21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] PL argument max size, and doubt
I was doing some tests to see if I could find a max size for an argument of type TEXT in a PL/PgSQL function (BTW, which it that limit if it exists?). So I made the function to test: CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ BEGIN EXECUTE $ins1$ INSERT INTO funcdatogrande VALUES (default,$ins1$ || quote_literal($1) || $ins2$)$ins2$; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $body$ LANGUAGE 'plpgsql'; What bothers me is that the INSERT passes ok (the data is inserted) but the function is returning false on any all to it. I hope not to have a conceptual problem. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL argument max size, and doubt
Rodrigo De León escribió: On Nov 21, 2007 8:23 AM, Martin Marques <[EMAIL PROTECTED]> wrote: (BTW, which it that limit if it exists?). "In any case, the longest possible character string that can be stored is about 1 GB." See: http://www.postgresql.org/docs/8.2/static/datatype-character.html I was asking about the limit in the argument. Is it the same as the limits the types have in table definition? So I made the function to test: CREATE OR REPLACE FUNCTION datoGrande(TEXT) RETURNS BOOLEAN AS $body$ BEGIN EXECUTE $ins1$ INSERT INTO funcdatogrande VALUES (default,$ins1$ || quote_literal($1) || $ins2$)$ins2$; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $body$ LANGUAGE 'plpgsql'; What bothers me is that the INSERT passes ok (the data is inserted) but the function is returning false on any all to it. I hope not to have a conceptual problem. I don't think EXECUTEing sets FOUND to true. Try: CREATE OR REPLACE FUNCTION DATOGRANDE(TEXT) RETURNS BOOLEAN AS $$ BEGIN INSERT INTO FUNCDATOGRANDE VALUES (DEFAULT,$1); IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$ LANGUAGE 'PLPGSQL'; I have always heard that modification queries should be EXECUTED in PL. AFAICR. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL argument max size, and doubt
Tom Lane escribió: > Martin Marques <[EMAIL PROTECTED]> writes: > >> I have always heard that modification queries should be EXECUTED in PL. >> AFAICR. > > Run far away from whatever source gave you that advice... Sorry, it was with DDL commands. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PL argument max size, and doubt
Tom Lane escribió: Martin Marques <[EMAIL PROTECTED]> writes: Tom Lane escribió: Martin Marques <[EMAIL PROTECTED]> writes: I have always heard that modification queries should be EXECUTED in PL. AFAICR. Run far away from whatever source gave you that advice... Sorry, it was with DDL commands. That's not much better ;-). DDL commands don't have plans, so there's not anything that could be invalidated. I don't see any reason to use an EXECUTE unless there's an actual textual change in the command you need to execute. Well, actually http://archives.postgresql.org/pgsql-sql/2007-02/msg00214.php See the follow-ups ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Syntax question: use of join/using with fully qualified table name
Bryce Nesbitt escribió: I've got a join where a certain column name appears twice: select username,last_name from eg_member join eg_membership using (member_id) join eg_account using (account_id) join eg_person using (person_id); ERROR: common column name "person_id" appears more than once in left table My first inclination was to fully quality the table name. Why would this not be acceptable syntax? select username,last_name from eg_member join eg_membership using (member_id) join eg_account using (account_id) join eg_person using (eg_member.person_id); ERROR: syntax error at or near "." at character 145 Did you read the manual? USING (join_column [, ...]) A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg_dump and "could not identify an ordering operator for type name"
Gerardo Herzig escribió: Hi dudes. Im facing a problem with pg_dump, pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not identify an ordering operator for type name HINT: Use an explicit ordering operator or modify the query. pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname as indexname, pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, t.relnatts as indnkeys, i.indkey, i.indisclustered, c.contype, c.conname, c.tableoid as contableoid, c.oid as conoid, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace, array_to_string(t.reloptions, ', ') as options FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN pg_catalog.pg_depend d ON (d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i') LEFT JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) WHERE i.indrelid = '7929896'::pg_catalog.oid ORDER BY indexname Dumping of other databases works fine. Looks like a corrupted internal table, isnt? Version og pg_dump and version of the PG server, ¿are they the same? Try connecting to *the problematic DB* and issue a query against pg_index to see if the catalog table is OK. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
