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,
S
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", "Exclu
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
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
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 usefu
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
>
> 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
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. a
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
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
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
--
Sen
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:
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)"
"
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 ~~ 'So
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,
S
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-redha
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
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
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
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
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
>
> 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 be
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
>
> 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
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 workaro
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
"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
"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( &
"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 evalua
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)
RET
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 A
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_str
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)---
>
> 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 t
>
> 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
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
"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]&
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
""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, pleas
""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
...
> 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 broad
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
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
-
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
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 pr
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
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 i
ed 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
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 secon
n 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:
>>
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
51 matches
Mail list logo
