[GENERAL] Get current trasanction id
Hello, is there any way to get current transaction id using plpgsql or sql ? Thanks in advance for any help. ML ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [SQL] Get current trasanction id
O Marek Lewczuk έγραψε στις Dec 27, 2004 : > Hello, > is there any way to get current transaction id using plpgsql or sql ? Maybe write a C function which calls GetCurrentTransactionId(). > > Thanks in advance for any help. > > ML > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Where is the error
Hi
I have the following function:
CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';
When I try to use it:
select int('t'::bool);
ERROR: syntax error at or near "(" at character 11
I am using PG8.0b1
Thank you.
Kaloyan
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Get current trasanction id
I'm sure there's many tricky ways, but one simple way would be to insert a row into a table and then grab its XMIN value... Hope this helps, On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote: > Hello, > is there any way to get current transaction id using plpgsql or sql ? > > Thanks in advance for any help. > > ML > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpCUafHbyqji.pgp Description: PGP signature
Re: [GENERAL] Where is the error
On Dec 27, 2004, at 6:42 AM, Kaloyan Iliev Iliev wrote:
CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';
The problem is that you have quoted the function name as "int". You
should only do this when you want to force the exact case of the name.
When you do it this way, you have to use double quotes when you refer
to the identifier:
select "int"('t'::boolean);
int
-
1
(1 row)
So in this case you probably want to call your function int -- without
quotes. By default, PostgreSQL folds the names to lowercase.
Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Where is the error
Kaloyan Iliev Iliev napisaÅ(a):
Hi
I have the following function:
select int('t'::bool);
ERROR: syntax error at or near "(" at character 11
"int" is reserved for integer type, so you should not use it. However if
you add
namespace before function name "public.int(TRUE)" then it should work.
ML
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Where is the error
10x John,
You are right.The problem is that the function was dumped with quotes
from pg_dump but I suppose the one who created it used quotes and that's
why it dumps in this way.
I will fix this in the dump.
Thanks again.
Kaloyan
John DeSoi wrote:
On Dec 27, 2004, at 6:42 AM, Kaloyan Iliev Iliev wrote:
CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';
The problem is that you have quoted the function name as "int". You
should only do this when you want to force the exact case of the name.
When you do it this way, you have to use double quotes when you refer
to the identifier:
select "int"('t'::boolean);
int
-
1
(1 row)
So in this case you probably want to call your function int -- without
quotes. By default, PostgreSQL folds the names to lowercase.
Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(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: [GENERAL] Where is the error
begin Kaloyan Iliev Iliev <[EMAIL PROTECTED]> wrote:
> Hi
> I have the following function:
> CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
I'm not sure. But 'int' is reserved, possibly is this the error.
CREATE OR REPLACE FUNCTION my_int (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';
test_db=# select my_int('t'::bool);
my_int
1
(1 Zeile)
end
Regards, Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)
---(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: [GENERAL] Where is the error
Thanks To you all.
The problem was that the int was reserved. When I try
CREATE FUNCTION my_int(boolean) RETURNS integer AS '
select CASE WHEN $1 THEN 1
ELSE 0
END ' LANGUAGE sql;
it worked.
But now I have to change it on many places. This is not good beacause on
the old version of PG (form where I dumped) everithing was OK:)
Kaloyan
Kaloyan Iliev Iliev wrote:
Hi
I have the following function:
CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';
When I try to use it:
select int('t'::bool);
ERROR: syntax error at or near "(" at character 11
I am using PG8.0b1
Thank you.
Kaloyan
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Where is the error
Hi again,
When I chage in the dump file
CREATE FUNCTION int (boolean) RETURNS integer AS '
select CASE WHEN $1 THEN 1
ELSE 0
END ' LANGUAGE sql;
ERROR: syntax error at or near "(" at character 21
I receive this message. So the question is how to create it without quotes.
Thank in advance:)
Kaloyan
John DeSoi wrote:
On Dec 27, 2004, at 6:42 AM, Kaloyan Iliev Iliev wrote:
CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';
The problem is that you have quoted the function name as "int". You
should only do this when you want to force the exact case of the name.
When you do it this way, you have to use double quotes when you refer
to the identifier:
select "int"('t'::boolean);
int
-
1
(1 row)
So in this case you probably want to call your function int -- without
quotes. By default, PostgreSQL folds the names to lowercase.
Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Get current trasanction id
On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote: > is there any way to get current transaction id using plpgsql or sql ? A couple of people have posted suggestions but I'll ask a question: Why do you want the transaction ID? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[GENERAL] error : syntax error at or near $1 for over select rows
This is the error i am getting when calling select * from
cas_reset_qi_changedate('CAS','2003-02-03'
ERROR: syntax error at or near "$1" at character 25
CONTEXT: PL/pgSQL function "cas_reset_qi_changedate" line 15 at for over
select rows
pgsql function :
CREATE or replace FUNCTION qe13.CAS_RESET_QI_CHANGEDATE (VARCHAR,TIMESTAMP)
RETURNS INTEGER AS '
DECLARE
AGR_UNID ALIAS for $1;
M_COMM_CLOSE_DATE ALIAS for $2;
DEFAULT_LOWEST_DATE timestamp default ''1900-01-01 00:00:00.0'';
vQuotaInstanceGID varchar(34) default null;
vQIStartDatetimestamp;
vQIEndDate timestamp;
for1record;
BEGIN
FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as
vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota
as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = q.model and
qi.quota = q.tril_gid LOOP
if (vQIStartDate > M_COMM_CLOSE_DATE OR vQIEndDate
<= M_COMM_CLOSE_DATE) then
update cm_quotainstance set changedate =
DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID;
end if;
END LOOP;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
I know the problem is that we cant use a variable name in a select query.
But then how can I use this variable later in "if" statement (say
vQIStartDate variable)
Thanks and Regards,
Vinita Bansal
_
NRIs send 10 photos FREE to India.
http://creative.mediaturf.net/creatives/icicibank/june/kodak/OTP.htm And win
a FREE ticket to India.
---(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: [GENERAL] 8.0 rc2 Problem
Peter Childs <[EMAIL PROTECTED]> writes: > 2. I loaded out 7.4 database on via a dump and reload. Once done I keep > getting errors when it trys to analyse (I'm using autovacuum) Nothing > unsuall just a straght forward pg_dump from 7.4 loaded into 8.0. > Dec 27 07:34:45 ex37 postgres[5745]: [28-1] ERROR: could not access > status of transaction 2684354560 Is this repeatable if you start over (re-initdb and reload)? If so I'd be very interested to see the dump file. regards, tom lane ---(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: [GENERAL] PostgreSQL training curriculum
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 21 Dec 2004, Jim C. Nasby wrote: I've been contracted to provide 3 days of PostgreSQL training, and I'm wondering if anyone has curriculum they'd like to share with me, or suggestions for course materials. This course is targeted at database experts who want to come up to speed on PostgreSQL, so it will deal mostly with installation, tuning, and troubleshooting. http://www.tdmsoft.com/en//PostgreSQL/pdf/TDM-PostgreSQL-Training-Curriculum.pdf is TDM's Training Curriculum for PostgreSQL. It is a bit old, but will be updated *after* 8.0.0 will be released. - -HTH Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFB0EC0tl86P3SPfQ4RAssUAKCe2CU6zgmGXQYKYVV+NvGmREZUwgCgnjL0 aoiF4HRRpkrSMMEby6yzU4s= =auKj -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 8.0 rc2 Problem
Tom Lane wrote: Peter Childs <[EMAIL PROTECTED]> writes: 2. I loaded out 7.4 database on via a dump and reload. Once done I keep getting errors when it trys to analyse (I'm using autovacuum) Nothing unsuall just a straght forward pg_dump from 7.4 loaded into 8.0. Dec 27 07:34:45 ex37 postgres[5745]: [28-1] ERROR: could not access status of transaction 2684354560 Is this repeatable if you start over (re-initdb and reload)? If so I'd be very interested to see the dump file. regards, tom lane I've dumped twice and reloaded twice same problem both times. Its difficult to let you see the dump as due to Data Protection. I re-inited once and reloaded just to check. I'll try a dump without data and see if that causes the same problem If that fails I'll can send you that. Then I might try adding data for tables that are not a risk. I'll do it tomarrow now, as I'm off as my Wife had a baby yestarday. Peter Childs ---(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: [GENERAL] 8.0 rc2 Problem
Peter Childs wrote: > Right just installed rc2 to test so that we can use 8.0 once its > out. Loads of nice features, Nice to see them all. Postgres 8.0 seams > faster, but this might be due to the reload. Oh the point in time > recovery feature are there any example scripts to say copy the files to > somewhere then print them onto cd or somthing once created. Oh and is it > possible to examin the point in time logs and see what happerened when, > ie which table got updated to what, very useful when research problems > later Probably would require some form of viewer I guess. > > 2 Problems encountered. > > 1. Confirguration file is completly different, so it you have one that > you have configured carfull for 7.4 it will not work at all with 8.0 > many configuration options have changed there names or changed how they > work. I can understand new ones and dropping some of the old ones but > why change it so complely? I would diff postgresql.conf against the share/postgresql.conf.sample and merge your changes into 8.0. We change it dramatically because we improve functionality and clarity. If we emphasized backward-compatibility it would be very unclear after a while. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] error : syntax error at or near $1 for over select rows
On Dec 27, 2004, at 11:36 AM, vinita bansal wrote: FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = q.model and qi.quota = q.tril_gid LOOP if (vQIStartDate > M_COMM_CLOSE_DATE OR vQIEndDate <= M_COMM_CLOSE_DATE) then update cm_quotainstance set changedate = DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID; end if; END LOOP; for1 is a record type from which you can access the other columns. So with something like FOR for1 in select * from cm_quotainstance where ... loop then you can access the columns in your subsequent if statements like: if (for1.startdate > > M_COMM_CLOSE_DATE OR for1.enddate <= M_COMM_CLOSE_DATE) ... Look in the plpgsql section of the documentation under "Looping Through Query Results" -- this is section 36.7.4 in the 8.0 documentation. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Get current trasanction id
Michael Fuhr napisaÅ(a): On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote: is there any way to get current transaction id using plpgsql or sql ? A couple of people have posted suggestions but I'll ask a question: Why do you want the transaction ID? What problem are you trying to solve? Michael, I've already solved the problem - I found somewhere on the web a very simple C function, which returns transaction id. I need transaction id for plperl functions setVar, getVar (using $_SHARED array). Functions can write/read variables which are available either for connection or transaction. Regards, ML ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
