[SQL] out of shared memory - find temporary tables
Hi there, I got "out of shared memory" error. Searching on postgresql forums, I found this it occurs probably because of intensive use of temporary tables in one transaction. I'm locking in pg_locks table, and I found some rows with the following modes: "ShareLock", "AccessExclusiveLock", "ExclusiveLock", "AccessShareLock", and "RowExclusiveLock" with many counts (especially "AccessExclusiveLock" and "AccessShareLock"), but the oid and relname is empty. I suppose there are related to some temporary tables. How can I find what are the storage procedures which create these temporary tables in my code ? TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how control update rows
Hi there, I'd like to control the rows which are updated. I found useful the option RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there ? I think to something like that: SELECT * FROM ( UPDATE "T" SET "C" = 1 WHERE "ID" > 100 RETURNING * ) x TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Removing CONTEXT message
Hi all, I know I can remove the CONTEXT messages from the file log with: SET log_error_verbosity = terse Is there any possibility to remove it from the client pgAdmin Query messages as well ? TIA, Sabin
Re: [SQL] Removing CONTEXT message
Hi Tom, In fact, I found setting the configuration parameter 'client_min_messages', I get the expected results according to the documentation, and it works equivalent to 'log_min_messages ' that controls the messages of the log file. I'd just wonder an equivalent parameter as 'log_error_verbosity', but for client messages, not log file. Is it something available ? TIA, Sabin "Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Sabin Coanda" <[EMAIL PROTECTED]> writes: >> I know I can remove the CONTEXT messages from the file log with: >> SET log_error_verbosity = terse >> Is there any possibility to remove it from the client pgAdmin Query >> messages as well ? > > This is the wrong place to ask; try the pgAdmin support lists. > > MHO: if they don't have a way to adjust the verbosity of their error > output, > they definitely should. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] alias not applied
Hi all, I have two queries: 1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS d ) x ORDER BY i 2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d UNION SELECT 51 AS i, true AS d ) x ORDER BY i The first returns the columns "PK_ID", "Deleted" The second returns the columns i, d. Why ? TIA, Sabin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] alias not applied
You are right. I use "PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows. But I just tried it on a newer version and it works well ( "PostgreSQL 8.1.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)" ). Thanks a lot, Sabin "Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Sabin Coanda" <[EMAIL PROTECTED]> writes: >> I have two queries: > >> 1. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, true AS >> ) x >> ORDER BY i >> 2. SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, true AS d >> UNION SELECT 51 AS i, true AS d ) x ORDER BY i > >> The first returns the columns "PK_ID", "Deleted" >> The second returns the columns i, d. > > Works for me: > > regression=# SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 52 AS i, > true AS d ) x ORDER BY i; > PK_ID | Deleted > ---+- >52 | t > (1 row) > > regression=# SELECT i AS "PK_ID", d AS "Deleted" FROM ( SELECT 49 AS i, > true AS d UNION SELECT 51 AS i, true AS d ) x ORDER BY i; > PK_ID | Deleted > ---+- >49 | t >51 | t > (2 rows) > > regression=# > > What PG version are you using, exactly? > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How compare current_setting(..) ?
Hi there, I'd like to check that my log_line_prefix is set to '%t'. I suppose I can check it with the following statement: SELECT current_setting( 'log_line_prefix' ) WHERE current_setting( 'log_line_prefix' ) != '%t' But it returns every time a row, with '%t', even when log_line_prefix is set to '%t'. What's wrong ? TIA, Sabin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How compare current_setting(..) ?
Sorry, it was my fault. The setting was '%t' and a space. I fixed it and it
works well.
Many thanks !
"Michael Fuhr" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Thu, Feb 22, 2007 at 12:25:42PM +0200, Sabin Coanda wrote:
>> I'd like to check that my log_line_prefix is set to '%t'.
>> I suppose I can check it with the following statement:
>> SELECT current_setting( 'log_line_prefix' )
>> WHERE current_setting( 'log_line_prefix' ) != '%t'
>>
>> But it returns every time a row, with '%t', even when log_line_prefix is
>> set
>> to '%t'.
>> What's wrong ?
>
> Works here. Might log_line_prefix have leading or trailing spaces?
> What does the following return?
>
> SELECT '<' || current_setting('log_line_prefix') || '>',
> length(current_setting('log_line_prefix'));
>
> --
> Michael Fuhr
>
> ---(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
>
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] column definition list of a dynamic record argument
Hi, I'd like to build a function which have a RECORD type input argument, and to find in the function body its associated column definition list. Is it posible ? TIA, Sabin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] reindex database
Hi, It would be so nice to use REINDEX DATABASE command without name, for the current database. Now the command is useless when a database schema script refers just itself, and the database name is not establish. I have to use REINDEX TABLE name instead searching all the database tables. What is your opinion ? Regards, Sabin ---(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
[SQL] array_to_string
Hi there, I used the function array_to_string, and I found it ignores NULL values, e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'. The function documentation doesn't explain this case. So please tell me: is it the normal behavior or a bug ? TIA, Sabin ---(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
[SQL] system table storing sequence attributes
Hi there, I'd like to read the global sequence attribute "currval", but not using currval function, because it is session dependent and requires a nextval function to be used before. Do you know where is stored this value in the system tables ? TIA, Sabin ---(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] system table storing sequence attributes
... > select last_value from sequence_name Unfortunately there is the same problem. The documentation say: "It is an error to call lastval if nextval has not yet been called in the current session." and I don't want to call nextval before. Sabin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] system table storing sequence attributes
""Marcin Stêpnicki"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
...
>
> I think that you either misunderstood this statement or try to break your
> application in a nasty way ;). Please tell us more about your problem
> and/or what do you want to achive, because sequences behave this way for a
> reason (concurrency issues). Perhaps there is another solution.
I have a table with a serial primary key aoto generated by a sequence. I
add/remove records. At a moment I'd like to know what is the current value
of the sequence. I don't wish to know this in the same session where I
add/remove records.
My Postgresql version is "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by
GCC gcc.exe (GCC) 3.4.2 (mingw-special)", on Windows XP OS, and I use
pgAdmin to open sessions on my database.
With this environment, try the following scenario:
Make a demo table:
CREATE TABLE "tbFoo"
(
"ID" integer NOT NULL DEFAULT nextval('"tbFoo_ID_seq"'::regclass)
)
At the beginning, no record are inserted in the table. I call:
SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') );
This rise the following error: ERROR: currval of sequence "tbFoo_ID_seq" is
not yet defined in this session
SQL state: 55000
Then I add a record there:
INSERT INTO "tbFoo" DEFAULT VALUES;
I call SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') ), and I get
1. That's ok because I just use indirectly a nextval to that sequence in the
insert process, on this session.
I close the session, and I open another one.
I call SELECT currval( pg_get_serial_sequence('"tbFoo"','ID') ), and I get
the error again:
ERROR: currval of sequence "tbFoo_ID_seq" is not yet defined in this session
SQL state: 55000
Sabin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] system table storing sequence attributes
""A. Kretschmer"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] ... > > Try 'select last_value from "tbFoo_ID_seq";' instead. > It works. Thanks a lot ! Sabin ---(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
[SQL] cluster index on primary key
Hi there, I'd like to know when I create a primary key on a table, does postgres will create automatically an index on that table related to the primary key columns, or I have to create it explicitly ? If I have to create it explicitly, suppose I have tables with a serial primary key, or link tables with multiple column primary key. How is recommended for the two cases: to create an index on the primary key unique, or cluster or both or with none of the two options ? TIA, Sabin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] array_to_string
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Sabin Coanda" <[EMAIL PROTECTED]> writes: >> I used the function array_to_string, and I found it ignores NULL values, >> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'. > > Do you have a better idea? > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate > I found the function would be useful to build dynamic statements that includes array items. For instance I have a function with an array argument of integer. In that function I'd like to send that argument by a dynamic string to other function. If array_to_string would return the whole elements, including NULLs, I'd be able to build that statement. But with the present behavior, the result statement damages the array structure. On the other hand, I saw there is available its complement function string_to_array. It would be nice to be able to reverse a string built by array_to_string, with string_to_array, getting the original array. This could happen if the function would consider the NULLs as well. A simple 'NULL' string would be enough to fulfil this reversing process, so that array_to_string( ARRAY[1,NULL,3], ',' ) would returns '1,NULL,3'. A problem will occur when it would be applied to string arrays, because NULL string, and 'NULL' string value would have the same result. This could be solved if the string arrays would be formed with the same rules as for SQL constant syntax. I mean with quotes. So that, array_to_string( ARRAY[ 'a', 'NULL', NULL, 'b'], '/' ) would return not a/NULL/NULL/b , but 'a'/'NULL'/NULL/'b'. Consequently, string_to_array function should interpret this result. Regards, Sabin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] ISO time zone format
Hi there, I'd like to format a time stamp with time zone as ISO pattern (e.g. 1999-01-08 04:05:06 -8:00) I found the patterns for all the timestamp parts, from "Data Type Formatting Functions" Postgresql documentation, excepting the numeric ISO time zone. Please suggest the appropiate pattern for that. TIA, Sabin ---(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] ISO time zone format
> > If your DateStyle is set to ISO that's the default format. You don't need > to use to_char() > > Or am I missing your point? > Hi Richard, I'd just like to format it independent of my DateStyle. Some timestamp parts may be formatted this way. For instance I can format ISO date with to_char( dt, '-MM-DD'), and ISO time with to_char( dt, 'HH24:MI:SS') as well. I'd just like to find the format pattern for the ISO time zone (numeric). Regards, Sabin ---(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] ISO time zone format
> > For the moment, you'll have to use extract() to pick out the bit(s) you > need: > to_char(extract(timezone_hour from current_timestamp), 'S00'); > It's perfect for me, thanks a lot. Sabin ---(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
[SQL] error dropping operator
Hi there, I don't have experience with operators. I built one and when I tried to drop it I got th efollowing error: cannot drop operator ... because it is required by the database system. How do I drop it please ? TIA, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] show value of backslashes in string array argument
Hi there,
I have a problem using backslash character as part of a string array item.
I use "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)" version, with standard_conforming_strings = 'on'.
I found that, is that in spite of using standard_conforming_strings = 'on',
the string array items are shown in C escape sequences conventions.
Use the following scenario:
- create a table with
CREATE TABLE test
(
"colVarchar" character varying,
"colVarcharArray" character varying[]
)
- insert a row with a string composed by just one character backslash,
and an array with just one item, with the same value of one backslash, with:
INSERT INTO test VALUES ( '\', ARRAY[ '\' ] );
- show the values with:
SELECT * FROM test
And the result is:
colVarchar | colVarcharArray
+-
\ | {"\\"}
The question is why the two strings are shown different in spite they are
the same, and standard_conforming_strings = 'on' ?
Sabin
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] plpgsql language not aware of standard_conforming_strings ?
Hi there, Having standard_conforming_strings = 'on', I build the following scenario. I request SELECT replace( 'a\b', '\', '\\' ), which get me the result: replace - a\\b I'd like to build a function that give me the same result, as: CREATE OR REPLACE FUNCTION "test"(s varchar) RETURNS varchar AS $BODY$ BEGIN RETURN replace( s, '\', '\\' ); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; But I rises the error: ERROR: unterminated string SQL state: 42804 Context: compile of PL/pgSQL function "test" near line 3 Ok, I suppose the function is not aware of standard_conforming_strings = 'on', so I have to change \ with \\. I make the following function: CREATE OR REPLACE FUNCTION "test"(s varchar) RETURNS varchar AS $BODY$ BEGIN RETURN replace( s, '\\', '' ); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The function is created without errors. But this is useless because SELECT test( 'a\b' ); returns a\b, and SELECT test( 'a\\b' ); returns ab. How can I get my desired function that means when I call test( 'a\b' ) it will return 'a\\b' ? TIA, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] show value of backslashes in string array argument
I fond another collateral problem, because there are the different
convention to describe a varchar array item which contains backslashes, when
standard_conforming_strings = 'on'
For instance, to get a string composed by just one character backslash I can
use any of the two forms:
SELECT ARRAY[ '\' ]::varchar[];
or
SELECT '{\\}'::varchar[];
On the other hand, standard_conforming_strings = 'on' let me use varchar
items with '\' format. So the first format seems to be aware of
standard_conforming_strings = 'on', but the second is not.
My problem is that the java driver build arrays using the second format, but
the driver seems to be aware of standard_conforming_strings = 'on'. This
make inconsistence using the statement parameters, because to get the same
thing in the database I have to initialize a varchar parameter with a string
of one backslashes, but a varchar array item has to be initialized with a
string of two backslashes.
Sabin
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] plpgsql language not aware of standard_conforming_strings ?
"Sabin Coanda" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] ... > > How can I get my desired function that means when I call test( 'a\b' ) it > will return 'a\\b' ? > The problem seems to be the constant evaluation in plpgsql functions which is not aware of standard_conforming_strings. An answer may be to build my own replace function, that doesn't use constant evaluation inside. For instance: CREATE OR REPLACE FUNCTION myreplace(sText varchar, sSrc varchar, sDst varchar) RETURNS varchar AS $BODY$ BEGIN RETURN replace( sText, sSrc, sDst ); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Using this function will give the expected result, when standard_conforming_strings = 'on', so SELECT myreplace( 'a\b', '\', '\\' ); will give the result 'a\\b' as expected. In fact this is an workaround :((. It would be nice to make the language to works like that :). Regards, Sabin ---(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] plpgsql language not aware of standard_conforming_strings ?
"Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> "Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> ...
>>
>> How can I get my desired function that means when I call test( 'a\b' ) it
>> will return 'a\\b' ?
>>
>
...
> CREATE OR REPLACE FUNCTION myreplace(sText varchar, sSrc varchar, sDst
...
Unfortunatelly this is not very productive when sSrc or sDst has to be
constants inside the function. There is another workaround for that, to
specify '\' as chr(92). For instance:
CREATE OR REPLACE FUNCTION myformat(sText varchar) RETURNS varchar AS
$BODY$
BEGIN
RETURN replace( sText, chr(92), '\\' );
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Consequently, the statement SELECT myformat('a\b' ) will get the desired
result a\\b
Sabin
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] show value of backslashes in string array argument
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > That's how it's supposed to be. See > http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876 Hi Tom, I read it and I understood there are 2 cascaded parsers, but I didn't find an explicit reference to the behavior related to standard_conforming_strings. But after some tests, I found the following behaviors: - the 1st parser is the SQL interpreter which is aware of standard_conforming_strings (on or off) - the 2nd parser which is an array interpreter, doesn't care of standard_conforming_strings, using every time C escape conventions Please confirm me whether I understand it correctly or not. TIA, Sabin ---(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] trap for any exception
That's ok, I found SQLSTATE and SQLERRM. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] trap for any exception
Hi there, I'd like to build a PL/pgSQL function which is able to generic trap any error, and interpret it. I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? TIA, Sabin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trap for any exception
> > I read 37.7.5. Trapping Errors, but the syntax requires to write > explicitly the exception condition, and not a generic one. > > Is it possible to build a generic trap or do you know a workaround for > that ? > Sorry, I found the OTHERS condition that trap any error. But the question still remains to find how could I interpret it and found the error code and message ? Sabin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] EXPLAIN ANALYZE inside functions
Hi there, I'd like to rise the performance of a custom function, but I read somewhere I cannot use EXPLAIN ANALYZE to get the execution plan of the code from the function. Do you know a workaround to do this ? I tried to apply EXPLAIN ANALYZE statements inside my procedure hopping I will get some notice messages, but I got nothing :(. TIA, Sabin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] EXPLAIN ANALYZE inside functions
> > Do you know a workaround to do this ? > I just succeeded adding the following code: FOR rec IN EXECUTE 'EXPLAIN ANALYZE ' || sSQL LOOP RAISE NOTICE ' - %', rec; END LOOP; Regards, Sabin ---(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] Aleatory exception
Sorry for the previous incomplete text. I add the followings: For instance I got that exception for the following statement string: '"my_function"( NULL, ''TEXT1'', NULL::int4, NULL::int4, 5413, , TIMESTAMP WITH TIME ZONE ''2007-11-23 10:08:29.904+02'', 19255, 0 )' What is wrong with this statement ? Regards, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Aleatory exception
Hi there, I have a custom function where I execute a dynamic statement. I trap the exceptions, and sometimes I got the following error data: SQLSTATE = 22P02, SQLERRM = invalid input syntax for integer: "NULL::int4" For instance I got this for the following statement string: Please explain what could be the reason ? TIA, Sabin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Aleatory exception - found cause
Hi, Finally I found the cause of the problem. I found deeper inside my calls, a function which has to return an integer column from a RECORD rec variable. The rec is assigned with the following statement: EXECUTE 'SELECT pk AS "PK" FROM "my_table" WHERE my_where_clause' INTO rec. When my_where_clause is not fulfilled, rec is initialized somehow with null. The exception is rised at the end where I call: RETURN rec."PK" Maybe someone can explain this behavior. TIA, Sabin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] execute system command from storage procedure
Hi there, Is it possible to execute a system command from a function ? (e.g. bash ) TIA, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] undefined relations in pg_locks
Hi there, I have a procedure where a dead-lock occurs, and I'm trying to find the tables involved in the lock. Unfortunatelly, I don't find the related objects of the oids of "relation" field. Also all the fields "classid" and "objid" are null. May I suppose there were references to temporary objects ? However, how cand I get the related objects involved in this lock ? TIA, Sabin ---(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
[SQL] undefined relations in pg_locks
Hi there, Sorry I repeat a problem which concerned me since two months, but I got no answer. It's not clear for me whether it is trivial or without a solution. I promisse not to repeat it in the future if I will give no answer this time too. So, I'm working with "PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)"; I have a procedure where a dead-lock occurs, and I'm trying to find the tables involved in the lock. A little description of how my procedure works maybe helps. So I have a loop where I call a second procedure with exception trapping. At its turn, it calls a third procedure. I guess an exception may occur in the third procedure, where I use a temporary table. When an exception occurs, I guess my temporary table table is not dropped, and this may lock the process when another call try to create that temporary table again. Could be this scenario what there happens to me ? Unfortunatelly, I don't find the related objects of the oids of "relation" field. Also all the fields "classid" and "objid" are null. May I suppose there were references to temporary tables? However, how cand I get the related objects involved in this lock, by other way than analyse deeply in the code ? TIA, Sabin -- 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] undefined relations in pg_locks
Oops! You are right. It was not a dead-lock. I let the process to continue and after about one hour I got in the log the following message 'cannot have more than 2^32-1 commands in a transaction'. After some investigations in the code I found an infinite loop. Thanks and sorry for wasting time, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to control the execution plan ?
Hi there,
I try to execute the following statement:
SELECT *
FROM (
SELECT "MY_FUNCTION_A"(bp."COL_A", NULL::boolean) AS ALIAS_A
FROM "TABLE_A" bp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."Editor"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL AND
bp."COL_A"::text <> ''::text
) x
WHERE (x.ALIAS_A::text ) IS NULL;
The problem is the excution plan first make Seq Scan on "TABLE_A", with
Filter: (("COL_A" IS NOT NULL) AND (("COL_A")::text <> ''::text) AND
(("MY_FUNCTION_A"("COL_A", NULL::boolean))::text IS NULL))". This way,
MY_FUNCTION_A crashes for some unsupported data provided by "COL_A".
I'd like to get an execution plan which is filtering first the desired rows,
and just after that compute te column value "MY_FUNCTION_A"(bp."COL_A",
NULL::boolean).
I made different combinations, including a subquery like:
SELECT *
FROM (
SELECT "MY_FUNCTION_A"(y."COL_A", NULL::boolean) AS ALIAS_A
FROM (
SELECT bp."COL_A"
FROM "TABLE_A" bp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."COL_E"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL
AND bp."COL_A"::text <> ''::text
) y
) x
WHERE (x.ALIAS_A::text ) IS NULL;
but postgres analyze is too 'smart' and optimize it as in the previous case,
with the same Seq Scan on "TABLE_A", and with the same filter.
I thought to change the function MY_FUNCTION_A, to support any argument
data, but the even that another performance problem will be rised when the
function will be computed for any row in join, even those that can be
removed by other filter.
Do you have a solution please ?
TIA
Sabin
--
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] how to control the execution plan ?
Hi Scott,
I add the answers below.
>
> Just wondering what the query plans look like here, both regular
> explain, and if you can wait for it to execute, explain analyze.
>
Just with explain, because the function craches when it is running:
"Merge Join (cost=141.41..188.32 rows=1 width=24)"
" Merge Cond: ("TABLE_C"."PK_ID" = bp."COL_C")"
" -> Merge Left Join (cost=62.33..96.69 rows=1000 width=44)"
"Merge Cond: (s."PK_ID" = "MY_FUNCTION_B".COL_D)"
"-> Index Scan using "TABLE_D_pkey" on "TABLE_D" s
(cost=0.00..18.49 rows=349 width=4)"
"-> Sort (cost=62.33..64.83 rows=1000 width=44)"
" Sort Key: "MY_FUNCTION_B".COL_D"
" -> Function Scan on "MY_FUNCTION_B" (cost=0.00..12.50
rows=1000 width=44)"
" -> Sort (cost=79.08..79.09 rows=1 width=28)"
"Sort Key: bp."COL_C""
"-> Hash Join (cost=10.59..79.07 rows=1 width=28)"
" Hash Cond: (bp."COL_B" = pn."PK_ID")"
" -> Seq Scan on "TABLE_A" bp (cost=0.00..68.46 rows=4
width=32)"
"Filter: (("COL_A" IS NOT NULL) AND (("COL_A")::text <>
''::text) AND ((("MY_FUNCTION_A"("COL_A", NULL::boolean))::text || '
'::text) IS NULL))"
" -> Hash (cost=10.50..10.50 rows=7 width=4)"
"-> Seq Scan on "TABLE_B" pn (cost=0.00..10.50 rows=7
width=4)"
" Filter: (("COL_E")::text ~~ 'Some%'::text)"
> I'm guessing that the function is not indexed / indexable. Is it
> marked immutable (and is it actually immutable) or stable (and is
> stable)?
>
The function is marked stable.
> If it's still to smart, you can run two queries, one to pull the set
> you want to work with from the custom function into a temp table, then
> analyze it, then run the query against that.
> Not an optimal solution, but it might be the fastest if you can't
> index your function.
>
In fact I would use that statement to define a permanent view, not in a
procedure.
Finally I found a trick specifying not just WHERE (x.ALIAS_A::text ) IS
NULL;, but combining with a constant and a join with a constant.
By the way, it doesn't works just with the constant or with the join :(
See the query and the plan below:
EXPLAIN SELECT *
FROM (
SELECT "MY_FUNCTION_A"(bp."COL_A", NULL::boolean) AS ALIAS_A, MY_AUX
FROM "TABLE_A" bp
CROSS JOIN (
SELECT '*'::character varying AS MY_AUX
) afp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."COL_E"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL
AND bp."COL_A"::text <> ''::text
) x
WHERE (x.ALIAS_A::text || ' ' || MY_AUX) IS NULL;
"Merge Join (cost=131.68..178.60 rows=1 width=56)"
" Merge Cond: ("TABLE_C"."PK_ID" = bp."COL_C")"
" -> Merge Left Join (cost=62.33..96.69 rows=1000 width=44)"
"Merge Cond: (s."PK_ID" = "MY_FUNCTION_B".COL_D)"
"-> Index Scan using "TABLE_D_pkey" on "TABLE_D" s
(cost=0.00..18.49 rows=349 width=4)"
"-> Sort (cost=62.33..64.83 rows=1000 width=44)"
" Sort Key: "MY_FUNCTION_B".COL_D"
" -> Function Scan on "MY_FUNCTION_B" (cost=0.00..12.50
rows=1000 width=44)"
" -> Sort (cost=69.36..69.36 rows=1 width=60)"
"Sort Key: bp."COL_C""
"-> Nested Loop (cost=10.59..69.34 rows=1 width=60)"
" Join Filter: "MY_FUNCTION_A"(bp."COL_A",
NULL::boolean))::text || ' '::text) || (afp.MY_AUX)::text) IS NULL)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Hash Join (cost=10.59..68.94 rows=22 width=28)"
"Hash Cond: (bp."COL_B" = pn."PK_ID")"
"-> Seq Scan on "TABLE_A" bp (cost=0.00..54.90
rows=862 width=32)"
" Filter: (("COL_A" IS NOT NULL) AND
(("COL_A")::text <> ''::text))"
"-> Hash (cost=10.50..10.50 rows=7 width=4)"
" -> Seq Scan on "TABLE_B" pn (cost=0.00..10.50
rows=7 width=4)"
"Filter: (("COL_E")::text ~~ 'Some%'::text)"
However I'm not sure there are no circumstances when the execution plan will
detect my trick and will optimize the query again :((
Sabin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 100% CPU at concurent access
Hi there,
I'm trying to solve the concurrent access in my database, but I found some
problems.
I use "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)";
I create the following scenario:
- use an empty database
- create the following table:
CREATE TABLE "TestTable"
(
"ID" integer NOT NULL,
CONSTRAINT "TestTable_pkey" PRIMARY KEY ("ID")
)
WITHOUT OIDS;
ALTER TABLE "TestTable" OWNER TO postgres;
- add a row in the table with
INSERT INTO "TestTable" VALUES ( 1000 );
- create the following functions:
CREATE OR REPLACE FUNCTION "TestProcInner"()
RETURNS integer AS
$BODY$
DECLARE
Loops int4 = 10;
BEGIN
FOR i IN 0..Loops LOOP
RAISE NOTICE '%',i;
UPDATE "TestTable" SET "ID" = i;
PERFORM pg_sleep(1);
END LOOP;
RAISE NOTICE 'SUCCEEDED';
RETURN 0;
EXCEPTION
WHEN serialization_failure THEN
RAISE NOTICE 'FAILED';
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "TestProcInner"() OWNER TO postgres;
and
CREATE OR REPLACE FUNCTION "TestProcOuter"()
RETURNS integer AS
$BODY$
DECLARE
Loops int4 := 1;
BEGIN
LOOP
RAISE NOTICE 'TestProcOuter: % loop', Loops;
IF 0 = "TestProcInner"() THEN
EXIT; -- LOOP
END IF;
Loops = Loops + 1;
END LOOP;
RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "TestProcOuter"() OWNER TO postgres;
I use the following procedure to check when the concurrency access occures:
- open two query windows in pgAdmin
- add the following script in the both windows:
BEGIN TRANSACTION;
SELECT "TestProcInner"();
COMMIT;
- run the script in the 1st window
- run the script in the 2nd window
- check the results:
- the script in the 1st window commit the transaction
and write the log message 'SUCCEEDED'
- the script from the 2nd window catch an exception
and write the log message 'FAILED'
Then I try to use the following procedure to catch the concurrency access
occurence and retry until both scripts succeed:
- open two query windows in pgAdmin
- add the following script in the both windows:
BEGIN TRANSACTION;
SELECT "TestProcOuter"();
COMMIT;
- run the script in the 1st window
- run the script in the 2nd window
- the Postgres begins to CONSUME 100% CPU, and LOCKS until I cancel the
connection
from other pgAdmin session
- after a few second the first window finishes with 'SUCCEEDED'
- the second window writes:
ERROR: canceling statement due to user request
Could somebody tell me why the procedure doesn't work and the CPU is used
100%, please ?
TIA,
Sabin
--
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] 100% CPU at concurent access
Hi Tom, Well, I thought the connection with the failed transaction checks in a loop until the succeeded transaction will finish, and then it will succeeded as well. However, would you suggest me a code for "TestProcOuter" that works and fulfils my desire, please ? :) Thanx, Sabin -- 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] 100% CPU at concurent access
I find the problem is in my outer procedure, because it has no sleep there, and I change it calling pg_sleep: -- Function: "TestProcOuter"() -- DROP FUNCTION "TestProcOuter"(); CREATE OR REPLACE FUNCTION "TestProcOuter"() RETURNS integer AS $BODY$ DECLARE Loops int4 := 1; BEGIN LOOP RAISE NOTICE 'TestProcOuter: % loop', Loops; IF 0 = "TestProcInner"() THEN EXIT; -- LOOP END IF; Loops = Loops + 1; PERFORM pg_sleep(4); END LOOP; RETURN 0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION "TestProcOuter"() OWNER TO postgres; With this change, I found the first session succeeds, the CPU is not rised anymore, but the second session doesn't succeed even after the first one finish successfully. It fails forever. Why ? What have I make to succeed ? TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] uuid on windows
Hi there, I installed Postgres "PostgreSQL 8.3.5, compiled by Visual C++ build 1400" on Windows OS, and I tried to check some uuid functions, for instance: SELECT uuid_generate_v1(); But I found the following error message: ERROR: function uuid_generate_v1() does not exist LINE 1: SELECT uuid_generate_v1() ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Why ? What I should install more to have uuid functions ? TIA, Sabin -- 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] uuid on windows
I run the following script and now it works: CREATE OR REPLACE FUNCTION uuid_generate_v1() RETURNS uuid AS '$libdir/uuid-ossp', 'uuid_generate_v1' VOLATILE STRICT LANGUAGE C; Similar for all the other defined uuid functions. Unfortunatelly I experienced the same problem on linux version 8.3.5. after I installed the package. But there the same script doesn't work, and logs that the file is not found. Sabin -- 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] uuid on windows
> > I *think* it's one of the optional modules with recent installers, but I > don't run Windows routinely, so you'll have to check. > I installed with full optional modules on Windows, but uuid is still missing. However, I found C:\Program Files\PostgreSQL\8.3\share\contrib\uuid-ossp.sql, I run it and it's ok now. Thanx, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] raise doesn't add end of line
Hi there, I upgrade "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" to "PostgreSQL 8.3.5, compiled by Visual C++ build 1400", and I found that many RAISE statements in a function no more adds end of line (CR/LF) on every message. Just when the funcion ends a CR/LF is added in the log. Is it the same behavior on linux os ? Do you know a trick to get my old end of lines ? TIA, Sabin -- 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] raise doesn't add end of line
Also I found all the characters E'\n' in a raise are filtered and not logged. I don't beleave no one knows how to log the end of line, maybe by a related configuration setting. Please help, or at least confirm there is a bug, to not waste time looking for workarounds. I'm disperate all my usefull multiline logs are now useless :( TIA, Sabin -- 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] raise doesn't add end of line
Hi Pavel, I tried on Windows, and it works. Thanks a lot. BTW, I didn't succeed to find char(10) workaround because I tried at the end of the line, where ... it doesn't work. For instance check the function: -- Function: r() -- DROP FUNCTION r(); CREATE OR REPLACE FUNCTION r() RETURNS void AS $BODY$begin raise notice 'Break the 1st%line...', chr(10); raise notice 'eol on 2nd line...%', chr(10); raise notice '3rd line'; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION r() OWNER TO postgres; Run it: SELECT r() And you get: NOTICE: Break the 1st line...NOTICE: eol on 2nd line...NOTICE: 3rd line -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] VACUUM cannot be executed from multi-command string
Hi there, I upgrade "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" to "PostgreSQL 8.3.5, compiled by Visual C++ build 1400". I found the new Postgres version doesn't allowed to run a script file which contains multiline statements, which was working at the previous version. Trying to do it I get the error: ERROR: VACUUM cannot be executed from a function or multi-command string This cause a bad performance running a script with more than 2 statements. I suppose this behavior is required to have transactional update on the whole script file, but this is a feature I didn't need in my scenario. In the previous version I made some tests with and without vacuum, and I found the following results: - without vacuum: 1000 rows in 1 min 2000 rows in 3 min 3000 rows in 7,5 min ... and the time rises geometrically, at 2 getting hours - with vacuum on every 20 statements 1000 rows in 30 sec 2000 rows in 70 sec 3100 rows in 120 sec ... and it rises lineary with the script length, at 2 getting about 10 minutes. Can anybody help me to find a solution with the new vacuum constraint, without loosing the performance ? TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
