[SQL] Concurrency problem
Hi, I have a problem with concurrency, where ordinary selects render me Warning: pg_query(): Query failed: ERROR: tuple concurrently updated CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in /home/site/PHP/db_func.php on line 301 Code of get_rights: GRANT SELECT ON c.users TO adm,w3; SELECT rights INTO result FROM c.users WHERE usr_id=uid; REVOKE ALL ON c.users FROM w3,adm; RETURN result; What function is in error at a specific point in time appears to be random. There are no "update" SQL commands issued between two loads (actually two meny selections in sequence, the first not being served completely). Still the db complain about "updates". I use triggers only at "create" and in some instances "update", not when doing "select". Tables are interconnected by foreign keys, sometimes mutliple. Is there anybody out there with similar experiences? Someone who might point in some directions in terms of documentation of *how* to design and use a database to avoid concurrency problems? Any thoughts on this are greatly appreciated. /Dag ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] date_trunc'd timestamp index possible?
I'm trying to create a index from a timestamp+tz field and want the index
to be date_trunc'd down to just the date
when i try to do a
create idxfoo on foo (date(footime));
i get a
ERROR: DefineIndex: index function must be marked IMMUTABLE
and it chokes on when i try to use the date_trunc() function as well
create idxfoo on foo (date_trunc('day',footime));
ERROR: parser: parse error at or near "'day'" at character 53
Any suggestions/workarounds (other than creating additional date-only
columns in the schema and indexing those???)
-d
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Error : could not send data to client
Hello, Perhaps could you help me... We work under Windows. Postgres is installed and functions in the form of service NT (postmaster). We also use a service "cron" which carries out regular tasks (same than the crontab under Linux). We added an order (carried out every hour) in the "crontab" who launches file Shell script. This last file carries out (with psql call) a function defined in our PostGres database. The task is well carried out but the function does not seem to be carried out or not called. If we carry out the same file Shell script under Cygwin, the function is called and all the treatment is carried out. For information, in the log postmaster, we have the following messages: < < < < I realize that my message is long, but I tried to describe the situation as well as possible (especially taking into account my english level). Thank you for your assistance. Créez gratuitement votre Yahoo! Mail avec 100 Mo de stockage ! Créez votre Yahoo! Mail Le nouveau Yahoo! Messenger est arrivé ! Découvrez toutes les nouveautés pour dialoguer instantanément avec vos amis. Téléchargez GRATUITEMENT ici !
Re: [SQL] SELECT with Function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Paulo Nievierowski wrote: | PS: Excuses my poor english. Your english is excellent. MY is poor!! | I create plpgsql function "myfunc(integer)" thats returns of type | record with values from table X (the function decides what record must | be select). The parameter is an keyprod existing in table X and in | table A. | | It run well sending in psql: | SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 numeric); | | The problem is: | I need return data from table A (this have column 'keyprod'). This | value (keyprod) must be passed as parameter to myfunc(). The result | must be a union of columns selected from table A and result of | myfunc(). | | How to create this query? It's possible? Yep. Look at this: drop table dupa cascade; create table dupa (a int, b int, c int); insert into dupa (a, b, c) values (1, 2, 3); insert into dupa (a, b, c) values (2, 3, 4); create or replace function ttt(int) returns record as ' declare ~r record; begin ~select * into r from dupa where ($1=a); ~return r; end; ' language 'plpgsql'; And then: ojciec=# select * from ttt(1) as foo(aa int, bb int, cc int); ~ aa | bb | cc - ++ ~ 1 | 2 | 3 Is this what you need? - -- ojciec -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFBXYnyCAdl/NTXOsERAs/EAKCUq26LmG9N36vW/WXGC4i92Ci4VwCdF+eS fiiHtfCVDONxxldr4SC17TI= =tahv -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?
> The reason this doesn't work is that the timestamp to date conversion
> depends on the time zone setting. In theory you should be able to avoid
> this by specifying the time zone to check the date in. I tried something
> like the following which I think should work, but doesn't:
> create idxfoo on foo (date(timezone('UTC',footime)));
>
> The conversion of the timestamp stored in footime should be immutable
> and then taking the date should work. I did find that date of a timestamp
> without time zone is treated as immutable.
>
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.
I think we found a way around it!
CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS
'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ;
CREATE INDEX "new_event_dt" ON "the_events" USING btree (
date_immutable( "event_dt_tm" ) ) ;
-
[EMAIL PROTECTED]BackPack Software, Inc.www.backpack.com
+1 651.645.7550 voice "Life is an Adventure.
+1 651.645.9798 faxDon't forget your BackPack!"
-
---(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]
Hi all! If I made a C function, let's say "ext_data", with an int input parameter and RECORD or user defined type (but always only one row) as a output, would I be able to write a SQL query like: SELECT st.*, ext.* FROM SomeTable st, ext_data(st.ID) ext WHEREcondition only on SomeTable data. Bosko Confidentiality Notice: This e-mail, including any documents accompanying this e-mail, contains information from the offices of TeleTrader Software AG, which may be confidential. This e-mail message is intended only for the addressee(s). If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this e-mail is prohibited. If this e-mail has been sent to you in error, please delete this e-mail and any copies or links to this e-mail completely from your system and notify us immediately so that we can ensure that no further such e-mails are sent to you. Please also keep in mind that communication via e-mail over the Internet is insecure because of third parties' possibilities to access and manipulate e-mails. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of TeleTrader Software AG. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Postgres Doubt
On Sep 27, 2004, at 11:27 PM, sreejith s wrote: Hello, I am new to PostgreSQL. I have a doubt. Now i am doing one project in .NET technology with postgres(linux) as database. I am using pgADMIN and pgManager as tools for database releted activities. Now i am able to create functions with 'sql' language. When i select 'pgsql' as language for creating query, an error poping up. How to create Query using 'pgsql' language. if any new tools need to be installed. Plz rectify. Mail me back at [EMAIL PROTECTED] You need to use the createlang utility to add pgsql to your database. see http://www.postgresql.org/docs/current/interactive/app-createlang.html http://www.postgresql.org/docs/current/interactive/sql- createlanguage.html You generally want to do this for template1 so it will be available in any new database you create. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Concurrency problem
Dag Gullberg <[EMAIL PROTECTED]> writes: > Warning: pg_query(): Query failed: ERROR: tuple concurrently updated > CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in > /home/site/PHP/db_func.php on line 301 > Code of get_rights: > GRANT SELECT ON c.users TO > adm,w3; > > SELECT rights INTO result > FROM c.users > WHERE usr_id=uid; > REVOKE ALL ON c.users FROM w3,adm; > RETURN result; Do you have a bunch of clients doing this in parallel? If so, the problem is probably coming from two instances of the function trying to update the catalog entry for c.users at the same time. The approach is fundamentally broken anyhow, because there is nothing stopping client 2 from revoking the rights in between client 1 doing his GRANT and his SELECT. The right way to do what you seem to want (allow only this function to access the table) is not to flap the rights settings back and forth like that. Grant SELECT rights to a specific userid and make the function be owned by that userid and be labeled SECURITY DEFINER. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] How to convert 3 colums to timestamp with timezone
Hi There! Hope this is an easy one :-D How do I convert 3 individuel colums with type "date", "time" and "integer" to at timestamp with timexone? eg. tabel cStartUnixtimecStartTimecStartZone 09-24-200410:27:12-2 This I have to present as an timestamp with timezone. Thanks in advance Jens Arnfelt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to convert 3 colums to timestamp with timezone
On Sun, Oct 03, 2004 at 22:50:13 +0200,
Jens Arnfelt <[EMAIL PROTECTED]> wrote:
> Hi There!
>
> Hope this is an easy one :-D
>
> How do I convert 3 individuel colums with type "date", "time" and
> "integer" to at timestamp with timexone?
>
> eg. tabel
> cStartUnixtimecStartTimecStartZone
> 09-24-200410:27:12-2
>
> This I have to present as an timestamp with timezone.
You can add the date and time to get a timestamp and then use AT TIME ZONE
with the second argument being the integer times an interval of 1 hour.
Sort of like the following:
bruno=> select ('2004-9-24'::date + '10:27:12'::time) at time zone (-2 * '1
hour'::interval);
timezone
2004-09-24 07:27:12-05
(1 row)
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Postgres Doubt
Hello, I am new to PostgreSQL. I have a doubt. Now i am doing one project in .NET technology with postgres(linux) as database. I am using pgADMIN and pgManager as tools for database releted activities. Now i am able to create functions with 'sql' language. When i select 'pgsql' as language for creating query, an error poping up. How to create Query using 'pgsql' language. if any new tools need to be installed. Plz rectify. Mail me back at [EMAIL PROTECTED] Sreejith ---(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
