[SQL] transaction isolationa level - SERIALIZABLE

Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an

Re: [SQL] transaction isolationa level - SERIALIZABLE

That's what I thought. Thank you. regards mk 2013/5/13 Adrian Klaver adrian.kla...@gmail.com On 05/13/2013 02:22 AM, Marcin Krawczyk wrote: Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm

Re: [SQL] ALTER USER abc PASSWORD - what's going on ???

Ok, thank you. pozdrowienia mk 2013/4/18 Guillaume Lelarge guilla...@lelarge.info On Thu, 2013-04-18 at 13:21 +0200, Marcin Krawczyk wrote: I figured it out... when changing role from pgAdmin, it has a default VALID UNTIL 1970 set and after confirming changes it just made my abc user

[SQL] ALTER USER abc PASSWORD - what's going on ???

Hi, I can't change the pass for my user. When I invoke ALTER USER abc PASSWORD newpassword it changes ok but then I can't login with my newpassword... It says authetication failed. My pg_hba.conf has md5 entry for the ip I connect from. It used to work ok, but I accidentaly changed the password

Re: [SQL] ALTER USER abc PASSWORD - what's going on ???

. pozdrowienia mk 2013/4/18 Marcin Krawczyk jankes...@gmail.com Hi, I can't change the pass for my user. When I invoke ALTER USER abc PASSWORD newpassword it changes ok but then I can't login with my newpassword... It says authetication failed. My pg_hba.conf has md5 entry for the ip I connect from

[SQL] locks problem

Hi list, I've got a locking problem which prevents me from doing some alters on my tables. When I looked into pg_locks I saw a lot of entries (around 1000) with pid being NULL and almost all of them are AccessShare. Can anyone tell me what might those be and how do I get rid of them ?

Re: [SQL] locks problem

Ok I figured it out. I had a prepared transaction holding the locks. The pg_prepared_xacts was helpful. 27-11-2012 10:27, Marcin Krawczyk jankes...@gmail.com napisał(a): Hi list, I've got a locking problem which prevents me from doing some alters on my tables. When I looked into pg_locks I

[SQL] regexp_replace behavior

Hi list, I'm trying to use regexp_replace to get rid of all occurrences of certain sub strings from my string. What I'm doing is: SELECT regexp_replace('F0301 305-149-101-0 F0302 {x1} 12W47 0635H {tt}{POL23423423}', E'\{.+\}', '', 'g') so get rid of whatever is between { } along with these,

Re: [SQL] regexp_replace behavior

Yes that's exactly what I needed. Thanks a lot. pozdrowienia mk 2012/11/20 Alvaro Herrera alvhe...@2ndquadrant.com Marcin Krawczyk escribió: Hi list, I'm trying to use regexp_replace to get rid of all occurrences of certain sub strings from my string. What I'm doing is: SELECT

[SQL] custom_variable_classes in 9.1

Hi list, I'm using some global variables through custom_variable_classes facility. I've recently switched from 8.1 to 9.1 and somewhere along the line the behavior of custom_variable_classes has changed - if the variable has not been set for a given session invoking it (SELECT

Re: [SQL] custom_variable_classes in 9.1

been asking for. pozdrowienia mk 2012/2/29 Marcin Krawczyk jankes...@gmail.com Hi list, I'm using some global variables through custom_variable_classes facility. I've recently switched from 8.1 to 9.1 and somewhere along the line the behavior of custom_variable_classes has changed

Re: [SQL] Function definitions - batch update

Thanks for the hints, I'll give it a try. pozdrowienia mk 2012/2/21 Tom Lane t...@sss.pgh.pa.us Marcin Krawczyk jankes...@gmail.com writes: I've come across a situation when I need to add some constant code to all functions in my database. Does anyone know a way to batch update all

[SQL] Function definitions - batch update

Hi list, I've come across a situation when I need to add some constant code to all functions in my database. Does anyone know a way to batch update all definitions ? I've got like 500 functions so doing it one by one will be time consuming. pozdrowienia mk

[SQL] conditional aggregates

Hi list, Can anyone advise me on creating an aggregate that would take additional parameter as a condition ? For example, say I have a table like this id;value 1;45 2;13 3;0 4;90 I'd like to do something like this SELECT min_some_cond_aggregate(value,0) FROM table to get the minimal value

Re: [SQL] conditional aggregates

Yeah I know but I'm having problems creating sfunc fuction for the aggregate. regards mk 2010/12/8 Pavel Stehule pavel.steh...@gmail.com Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk

Re: [SQL] conditional aggregates

Thanks, it working. pozdrowienia mk 2010/12/8 Marc Mamin m.ma...@intershop.de something like ? Select min (case when X 0 then X end) HTH, Marc Mamin *From:* pgsql-sql-ow...@postgresql.org [mailto: pgsql-sql-ow...@postgresql.org] *On Behalf Of *Marcin Krawczyk *Sent

Re: [SQL] help

Or even simpler, or easier to understand: SELECT trim(foo, '()') FROM foobar pozdrowienia / regards / salutations mk 2010/5/5 Nicholas I nicholas.domni...@gmail.com Hi, I have a table in which the data's are entered like, Example: One (1) Two (2) Three (3) I want to extract the

Re: [SQL] pgAgent stats

jsljstid = 5 ORDER BY jslstart DESC LIMIT 0 obviously the problem is LIMIT 0 clause but why it is there remains a mystery... pgAdmin bug ? a configuration issue ? regards mk 2010/3/17 Guillaume Lelarge guilla...@lelarge.info Hi, Le 16/03/2010 08:40, Marcin Krawczyk a écrit : Hi list, does

Re: [SQL] pgAgent stats

Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when 0 :)) a bug ? pozdrowienia / regards / salutations mk 2010/3/17 Dave Page dp...@pgadmin.org On Wed, Mar 17, 2010 at 12:15 PM, Marcin Krawczyk jankes...@gmail.com wrote: It's pgAdmin 1.10.0

Re: [SQL] pgAgent stats

Yeah... my bad. Sorry for being a pain in the a... ;) pozdrowienia mk 2010/3/17 Dave Page dp...@pgadmin.org On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk jankes...@gmail.com wrote: Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when

Re: [SQL] pgAgent stats

Thanks for your help guys. regards mk 2010/3/17 Dave Page dp...@pgadmin.org On Wed, Mar 17, 2010 at 12:24 PM, Marcin Krawczyk jankes...@gmail.com wrote: Ha, it worked :)) kind of funny though. It was set to 0 but it's supposed to not limit anything when 0 :)) a bug ? What makes you

[SQL] pgAgent stats

Hi list, does anyone know the reason for pgAdmin not showing the stats neither for selected pgAgent step nor whole job ? The pga_joblog and pga_jobsteplog both get populated with data on run so I was thinking that maybe I'm missing some view associated with statistics tabs ?? Any ideas ? regards

Re: [SQL] Trapping 'invalid input syntax for integer'

Hi, I believe you're looking for invalid_text_representation. EXCEPTION WHEN invalid_text_representation THEN regards mk 2009/9/10 Mario Splivalo mario.spliv...@megafon.hr Is there a way to trap this error in plpgsql code? I have a function that accepts integer and character varying.

[SQL] skip if latter value equal

Hi list, I was wondering if it was possible for a field in SQL query to return NULL if latter value is exactly the same ? - for given ORDER BY clause, I guess. For example, query returns: xxyy 1 4 true xxyy 5 7 true xxyy 21 8 true yyzz 5 1 false yyzz 7 7 false yyzz 8 34 false I'd

Re: [SQL] skip if latter value equal

result; end if; s := r; end loop; return; end; $$ language plpgsql; select * from foo(); regards Pavel Stehule 2009/7/10 Marcin Krawczyk jankes...@gmail.com: Hi list, I was wondering if it was possible for a field in SQL query to return NULL if latter value is exactly

Re: [SQL] cast bool/int

This: SELECT true::integer, false::integer also works on 8.1 -- regards mk 2009/3/23 Achilleas Mantzios ach...@matrix.gatewaynet.com Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: Hi, I need a casting operator from boolean to integer, tu put in ALTER TABLE statment

Re: [SQL] array variables

I know I can do one column like this : a := ARRAY(SELECT id FROM idx); but how about more than one ? Because if I try this : a := ARRAY(SELECT id, p FROM idx); I get ERROR: subquery must return only one column SQL state: 42601 regards mk

[SQL] trigger parameters, what am I doing wrong ??

Hi guys. I'm trying to pass a parameter to trigger procedure but it's not working and I have no idea why since it looks pretty ok. Here's what I do : CREATE OR REPLACE FUNCTION test_proc() RETURNS trigger AS $BODY$ DECLARE chk boolean; parinteger := TG_ARGV[0]; BEGIN RAISE NOTICE

[SQL] return setof record - strange behavior

Hi everybody. Can anyone enlighten me what's wrong with this function : CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT ro integer, OUT mi integer) RETURNS SETOF record AS $BODY$ DECLARE w record; cy integer := EXTRACT (YEAR FROM current_date); BEGIN FOR w IN

[SQL] return setof record - strange behavior

The function behaves as expected when in plain SQL, only plpgsql function has the above mentioned problem. regards mk -- 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] return setof record - strange behavior

Dzieki za odpowiedz. Ciekawe ze funkcja SQL dziala bez problemu - ale tu juz trzeba wskazac parametry OUT. Thanks for your answer. It's curious that SQL function works as expected - but requires OUT params. pozdrowienia/regards mk 2008/8/4 Pawel Socha [EMAIL PROTECTED]: 2008/8/4 Marcin

[SQL] regexp_replace

Hi all. I'd like to know whether it's possible to reverse the behaviour of regexp_replace, meaning : now if I do SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it replaces the string that matches given pattern with 'X', how do I achieve the opposite - replace the string that

Re: [SQL] regexp_replace

thanks / dzieki regards / pozdrowienia mk 2008/8/1 Pawel Socha [EMAIL PROTECTED]: 2008/8/1 Marcin Krawczyk [EMAIL PROTECTED] Hi all. I'd like to know whether it's possible to reverse the behaviour of regexp_replace, meaning : now if I do SELECT regexp_replace ('foobarbaz', 'b..', 'X') I

[SQL] record type

Or maybe anyone knows how to work with record types ? How to insert something like ('1','2','3') into a table, or split it ? Anything ? regards mk

Re: [SQL] record type

Nice thanks a lot. Niezłe, dzieki. regards pozdrowienia mk 2008/7/11 Pawel Socha [EMAIL PROTECTED]: 2008/7/10 Marcin Krawczyk [EMAIL PROTECTED]: Hi. I need to know whether it's possible for a plpgsql function to accept record type parameters ? Is there a way to accomplish that ? I need

[SQL] record type

Hi. I need to know whether it's possible for a plpgsql function to accept record type parameters ? Is there a way to accomplish that ? I need to use something like ('1','2','3') as a parameter. regards mk

[SQL] exception handling and CONTINUE

Hi all. Can anyone tell me if there's a way to use CONTINUE clause outside the loop ? An example : FOR a IN SELECT * FROM xxx LOOP INSERT INTO yyy VALUES (a.***, ..) END LOOP; EXCEPTION WHEN unique_violation THEN CONTINUE; I get an error saying I can't use CONTINUE outside of a loop. Is there

Re: [SQL] exception handling and CONTINUE

Thank you guys. Alvaro your idea works tha way I wanted. Why didn't I think about it ? :) regards mk

[SQL] triggers order

Hi all. Today my question is about the order triggers are fired on a table. Is there a way to determine that order? Or what interests me even more, can I adjust the order triggers are fired? Regards mk

Re: [SQL] triggers order

Thanks for a fast answer. Frankly I was hoping it would be alphabetical ;) regards mk 2008/4/18, A. Kretschmer [EMAIL PROTECTED]: am Fri, dem 18.04.2008, um 11:44:12 +0200 mailte Marcin Krawczyk folgendes: Hi all. Today my question is about the order triggers are fired on a table

[SQL] error codes

Hi guys. Does anyone know the error code for '*currval of sequence * is not yet defined in this session*' error ? Is there one at all? I am aware of *others *code but I'd like to avoid using that. Thanks in advance. Regards mk

[SQL] connections between servers

Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be

Re: [SQL] connections between servers

Thanks a lot, I'll give it a try. regards mk 2008/4/3, Shane Ambler [EMAIL PROTECTED]: Marcin Krawczyk wrote: Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows

Re: [SQL] aggregate reverse

Yes, you got me well. That's exactly what I was trying to achieve. Thank you. 2008/2/1, Pavel Stehule [EMAIL PROTECTED]: Hello I am not sure if I understand well. On 01/02/2008, Marcin Krawczyk [EMAIL PROTECTED] wrote: Hi all. I wolud like to know whether it is possible to reverse

[SQL] aggregate reverse

Hi all. I wolud like to know whether it is possible to reverse the behaviour of an aggregate? Say I have a string '1,2,3,4'. Is there a way to split those values to records? Regards and thanks in advance. mk

[SQL] age() function usage

Hi all. I am trying to determine the way to pass a variable/field value to an age() function, query looks something like: SELECT age(timestamp data_zakonczenia_fakt) FROM kip_pracownicy_umowy WHERE id_pracownika = 8 data_zakonczenia_fakt being char column equal to say '1993-11-30'. Such approach

Re: [SQL] age() function usage

Heh, that was easy, I must have been working for too long... :) Thanks 2008/1/25, Scott Marlowe [EMAIL PROTECTED]: On Jan 25, 2008 1:06 PM, Marcin Krawczyk [EMAIL PROTECTED] wrote: Hi all. I am trying to determine the way to pass a variable/field value to an age() function, query looks

[SQL] table column names - search

Hi all. I would like to know if there's a way to obtain a list of tables containing specified column name? Using standard LIKE '%string' syntax would be great. Regards, mk

Re: [SQL] table column names - search

Thanks a lot. 2008/1/14, Peter Eisentraut [EMAIL PROTECTED]: Am Montag, 14. Januar 2008 schrieb Marcin Krawczyk: Hi all. I would like to know if there's a way to obtain a list of tables containing specified column name? Using standard LIKE '%string' syntax would be great. SELECT

[SQL] temp table existence

Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is not enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from

Re: [SQL] temp table existence

since since they may still be in use. 2007/12/29, Marcin Krawczyk [EMAIL PROTECTED]: Hi all. Is there a way to determine the existence of a TEMP TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is not enough because there may be other

Re: [SQL] temp table existence

I just realized something... my bad. It will work since TRUNCATE removes only table from current session. Thank you again. Regards

[SQL] raise exception and transaction handling

Hi, I have a problem with transaction handling. What I need to do is execute an INSERT command that would not be canceled by the RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code: BEGIN -- some computations bledne := (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || '