[SQL] reliable lock inside stored procedure
Hi, I have a stored procedure and I need a SHARE ROW EXCLUSIVE lock in this procedure (otherwise data will get corrupted). According to the documentation the LOCK statement is useless and will silently fail if not executed inside a transaction. (btw: this sounds dangerous to me) Also it is not possible to start a transaction inside a stored procedure. How can I make the code of this stored procedure safe? is there a way to let the procedure fail with an error if not executed within a transaction. does adding a SAVEPOINT the trick? documentation says that savepoints can only be established inside transactions, but does it fail fatal enough so that the procedure getss aborted? (more fatal than LOCK does?) thank you very much. /sebastian -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] reliable lock inside stored procedure
Sebastian Böhm wrote: > Hi, > > I have a stored procedure and I need a SHARE ROW EXCLUSIVE lock in this > procedure (otherwise data will get corrupted). OK. PostgreSQL doesn't have "stored procedures" so I guess you're talking about a function. > According to the documentation the LOCK statement is useless and will > silently fail if not executed inside a transaction. (btw: this sounds > dangerous to me) I'm not sure what you mean here, and I don't think you've understood the documentation. It's not possible to have a LOCK statement outside of a transaction. It's just not meaningful to have a transaction that only has a LOCK statement in it. > Also it is not possible to start a transaction inside a stored procedure. All functions execute within a transaction. As do all other SELECT, UPDATE, INSERT, DELETE, CREATE TABLE etc. If you (or your client library) do not supply a BEGIN then the command is treated as though BEGIN...COMMIT surrounded it. You can't have nested transactions because the COMMIT of a subtransaction wouldn't be meaningful. You can use SAVEPOINTS to roll back to a known point. > How can I make the code of this stored procedure safe? > > is there a way to let the procedure fail with an error if not executed > within a transaction. You can't execute outside of a transaction. It's not possible. > does adding a SAVEPOINT the trick? > documentation says that savepoints can only be established inside > transactions, but does it fail fatal enough so that the procedure getss > aborted? (more fatal than LOCK does?) I'm not sure I understand what you mean here. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] reliable lock inside stored procedure (SOLVED)
Hi Richard, thank you for your answer! Am 03.11.2008 um 12:06 schrieb Richard Huxton: Sebastian Böhm wrote: Hi, I have a stored procedure and I need a SHARE ROW EXCLUSIVE lock in this procedure (otherwise data will get corrupted). OK. PostgreSQL doesn't have "stored procedures" so I guess you're talking about a function. yes According to the documentation the LOCK statement is useless and will silently fail if not executed inside a transaction. (btw: this sounds dangerous to me) I'm not sure what you mean here, and I don't think you've understood the documentation. It's not possible to have a LOCK statement outside of a transaction. It's just not meaningful to have a transaction that only has a LOCK statement in it. as postgres does not warn you about this, this may lead to not so easy to spot bugs. If you forget to start a transaction and assume that you got a lock while modifieing a table, you can corrupt data. Also it is not possible to start a transaction inside a stored procedure. All functions execute within a transaction. As do all other SELECT, UPDATE, INSERT, DELETE, CREATE TABLE etc. If you (or your client library) do not supply a BEGIN then the command is treated as though BEGIN...COMMIT surrounded it. You can't have nested transactions because the COMMIT of a subtransaction wouldn't be meaningful. You can use SAVEPOINTS to roll back to a known point. so a call to a function is ALLWAYS a transaction ? good then I have no problem... How can I make the code of this stored procedure safe? is there a way to let the procedure fail with an error if not executed within a transaction. You can't execute outside of a transaction. It's not possible. does adding a SAVEPOINT the trick? documentation says that savepoints can only be established inside transactions, but does it fail fatal enough so that the procedure getss aborted? (more fatal than LOCK does?) I'm not sure I understand what you mean here. I assumed that a function can be executed without a transaction, means: every statement in the function is its own transaction. I understood that this is not the case. As SAVEPOINTS failes outside of a transaction I could then be used to detect wether there is a transaction already started or not. Imagine that you have a function in your code (not a postgres- function, but a C function) and this functions issues some statements that may corrupt data if no transaction is already created. You may then use SAVEPOINT in that c-function to make sure that there is a transaction started, as SAVEPOINT creates an exception if no transaction was started. /sebastian -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Date Index
Incidentally, extract(date from ts) doesn't work on my install of 8.3 (standard Ubuntu Hardy apt install). I get a "timestamp units "date" not recognized" error when I try it. The field I'm trying to create it on is "timestamp without time zone". -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: Friday, October 31, 2008 1:49 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Date Index am Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes: > Hey all, > > > > I?m apparently too lazy to figure this out on my own so maybe one of you can > just make it easy on me. J > > > > I want to index a timestamp field but I only want the index to include the > -mm-dd portion of the date, not the time. I figure this would be where the > ?expression? portion of the CREATE INDEX syntax would come in, but I?m not sure > I understand what the syntax would be for this. > > > > Any suggestions? Sure. You can create an index based on a function, but only if the function is immutable: test=# create table foo (ts timestamptz); CREATE TABLE test=*# create index idx_foo on foo(extract(date from ts)); ERROR: functions in index expression must be marked IMMUTABLE To solve this problem specify the timezone: For the same table as above: test=*# create index idx_foo on foo(extract(date from ts at time zone 'cet')); CREATE INDEX If you have a timestamp whitout timezone it is much easier: test=# create table foo (ts timestamp); CREATE TABLE test=*# create index idx_foo on foo(extract(date from ts)); CREATE INDEX Hope thats help, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Date Index
"Ryan Hansen" <[EMAIL PROTECTED]> writes: > Incidentally, extract(date from ts) doesn't work on my install of 8.3 The field names recognized by extract() are quite well documented http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT and "date" isn't one of them. What you probably want is just to cast the timestamp to date: cast(ts as date)-- SQL standard syntax ts::date-- traditional Postgres abbreviation regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
